IF function that outputs text with embedded formulas

Hello,

I am trying to use this if function in my report, but the imbedded variables are not showing the data they are linked to.

ie - it says {@.FX Currency} instead of EURO

I tried moving the quotes around, but it did not help. Any assistance is appreciated.

if {@.Hedge Des} = "Cash Flow"
then
"To offset changes in the spot price of {@.FX Currency}. This is accomplished by {@.Buy or Sell}ing {@.FX Currency} forward and excluding the forward points from the hedge effectiveness assessment."
else
"To offset changes in the spot price of {@.FX Currency}. This is accomplished by {@.Buy or Sell}ing {@.FXCurrency} forward, with the notional value of the hedge equal to the principal value of the loan."try this:

if {@.Hedge Des} = "Cash Flow"
then
"To offset changes in the spot price of "&{@.FX Currency}&". This is accomplished by "&{@.Buy or Sell}&"ing "&{@.FX Currency}&" forward and excluding the forward points from the hedge effectiveness assessment."
else
"To offset changes in the spot price of "&{@.FX Currency}&". This is accomplished by "&{@.Buy or Sell}&"ing "&{@.FXCurrency}&" forward, with the notional value of the hedge equal to the principal value of the loan."|||Worked like a charm! Thanks so much!

IF EXISTS

I wanna check the linked server is exist then do nothing and if not then
create linked server. Any command ?
like
if not exists()
begin
EXEC sp_addlinkedserver 'FASAT\FASATBASE'
EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
'testing'
end
Thanks
David
David,
You can try something like:
if not exists
(select * from sysservers where srvname like
'YourLinkedServerName')
-Sue
On Wed, 26 Jan 2005 11:51:32 -0500, "David"
<david@.north.com> wrote:

>I wanna check the linked server is exist then do nothing and if not then
>create linked server. Any command ?
>like
>if not exists()
>begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
>'testing'
>end
>
>Thanks
>David
>
|||IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
Keith
"David" <david@.north.com> wrote in message
news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I wanna check the linked server is exist then do nothing and if not then
> create linked server. Any command ?
> like
> if not exists()
> begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
> 'testing'
> end
>
> Thanks
> David
>
|||Thanks sue
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bvkfv0putlakl00d3qsh4sf91msjhcntg4@.4ax.com...
> David,
> You can try something like:
> if not exists
> (select * from sysservers where srvname like
> 'YourLinkedServerName')
> -Sue
> On Wed, 26 Jan 2005 11:51:32 -0500, "David"
> <david@.north.com> wrote:
>
|||Howdy Yall,
ALA, like this:
<@.sServerName, @.sSrvProduct, @.sProviderString, @.sPathToMdb are passed-in>
--Kill the server IF it exists and get rid of the user logins too!
IF EXISTS(SELECT * FROM master..sysservers where srvname=@.sServerName)
EXEC sp_dropserver @.server = @.sServerName, @.droplogins = 'droplogins'
--This will create the LINK
EXEC sp_addlinkedserver @.server = @.sServerName,
@.srvproduct = @.sSrvProduct,
@.provider = @.sProviderString,
@.datasrc = @.sPathToMdb
IF @.@.ERROR=0
EXEC sp_addlinkedsrvlogin @.sServerName, 'false', NULL, NULL, NULL
Regards,
jetgeek
"Keith Kratochvil" wrote:

> IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
>
> --
> Keith
>
> "David" <david@.north.com> wrote in message
> news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
>

Identity_Insert is set to OFF

Hi,
I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
to Duplicate a record from a form and the linked records in its subform.
Parent form duplicate goes to table "Jobs" where JobID is the key, records
from subform with (Link JobID) go to table "Samples". I've created an update
query in order to do this and I'm getting the following error-message:
Cannot insert explicit value for identity column in table "Jobs" when
IDENTITY_INSERT is set to OFF
Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
other way to duplicate these records?
Thanks in advance
gaba
Sorry,
I've meant "append query" not update
gaba
"gaba" wrote:

> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
> Thanks in advance
>
> --
> gaba
|||gaba wrote:
> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
>
Hi gaba,
The clue is in the question :-)
In order to insert data into a table which has an identity column, if
you want to insert a value instead of accepting the default, do the
following:
SET IDENTITY_INSERT <table name> ON
then doing your inserts, and then
SET IDENTITY_INSERT <table name> OFF
Rather irritatingly, you can only set this option against one table at
a time.
|||Thanks Damien. That was it. So simple...
gaba
"Damien" wrote:

> gaba wrote:
> Hi gaba,
> The clue is in the question :-)
> In order to insert data into a table which has an identity column, if
> you want to insert a value instead of accepting the default, do the
> following:
> SET IDENTITY_INSERT <table name> ON
> then doing your inserts, and then
> SET IDENTITY_INSERT <table name> OFF
> Rather irritatingly, you can only set this option against one table at
> a time.
>

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.
You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[table
name] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call tha
t
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP
--

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
--
David Portas
SQL Server MVP
--

IDENTITY column in SQL 2000 and linked tables in MS Access

Please help

We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?

Regards Anna-LenaIt all depends

How is the application written...

Do all of your controls have a data source property that is a table in SQL Server?

Or do you use unbound controls...

You might be better off posting here though

http://www.dbforums.com/forumdisplay.php?f=84

I doubt it's a problem with IDENTITY though|||The problem is that Access does not work will with large numbers of users. Anything above six to ten simultaneous users can cause problems.
I assume you have a form linked to a table or a view in your SQL Server database. Well, Access likes to copy down the ENTIRE recordset so that you can step through the results. When you have several people who each have loaded their own local copies of the same recordset and then try inserting a new record, I'd guess it plays havoc with the locking.
You might try having each user download a filtered subset of the data, reducing the number of copies of the same record that are spread over multiple terminals.|||The only way this works is to have all unbound controls, using rs.whatever and write code to fill in the controls, and to perform dml back to the database|||Which, in my opinion, takes away most of the advantages of using MS Access. So the application may as well be written on a more robust platform.|||Which, in my opinion, takes away most of the advantages of using MS Access. Apart from it being a one stop shop GUI\ report generator that can be distributed with xcopy you mean? A disconnected Access app is by no means the best solution in many situations but I would use one over a linked Access version any day**. And I would use Access over (for example) .NET in many circumstances too. In fact I do.

EDIT - ** Actually I got over excited and fibbed there. A disconnected app might be overdesigned for many applications (e.g. those that are accessed by a very small number of people).|||I use Access for rapid application development of apps with low user counts, and for that ADP files work fine. I never use linked tables, though.