By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,161 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,161 IT Pros & Developers. It's quick & easy.

need help with IIF statement

P: n/a
I have an exception table [System exception] that I want to use to
override the "system" for 11 records and use another table
([dbodtlrecs] for the rest of the records. When I used the following
IIF statement the XTAB query returns the correct "system" for the
exceptions, but the field is blank for the rest of the records.

System: IIf([dbodtlrecs].[ id]="3940" Or "3994" Or "5205" Or "6052" Or
"7467" Or "7740" Or "8020" Or "AE7626" Or "AE7664" Or "GT7090" Or
"GT7216" Or "TA00008",[System exception].[System],[ dbo dtlrecs].
[sysid])

I left joined ([dbodtlrecs].[ id] to [System exception].[contracts].

Any help is appreciated.

Oct 4 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
fo***********@gmail.com wrote in
news:11**********************@g4g2000hsf.googlegro ups.com:
I have an exception table [System exception] that I want to use to
override the "system" for 11 records and use another table
([dbodtlrecs] for the rest of the records. When I used the
following IIF statement the XTAB query returns the correct
"system" for the exceptions, but the field is blank for the rest
of the records.

System: IIf([dbodtlrecs].[ id]="3940" Or "3994" Or "5205" Or
"6052" Or "7467" Or "7740" Or "8020" Or "AE7626" Or "AE7664" Or
"GT7090" Or "GT7216" Or "TA00008",[System exception].[System],[
dbo dtlrecs]. [sysid])

I left joined ([dbodtlrecs].[ id] to [System
exception].[contracts].

Any help is appreciated.
I'm surprised it doesn't give an error message
You need to specify the field for each comparison

IIf([dbodtlrecs].[ id]="3940" Or [dbodtlrecs].[ id]="3994" Or
[dbodtlrecs].[ id]="5205"

Alternatively, you can use the IN operator
IIf([dbodtlrecs].[ id] IN ("3940", "3994", "5205", "6052")

It's a lot easier.to maintain.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Oct 4 '07 #2

P: n/a
On Oct 4, 4:13 pm, Bob Quintal <rquin...@sPAmpatico.cawrote:
foster.mar...@gmail.com wrote innews:11**********************@g4g2000hsf.googleg roups.com:


I have an exception table [System exception] that I want to use to
override the "system" for 11 records and use another table
([dbodtlrecs] for the rest of the records. When I used the
following IIF statement the XTAB query returns the correct
"system" for the exceptions, but the field is blank for the rest
of the records.
System: IIf([dbodtlrecs].[ id]="3940" Or "3994" Or "5205" Or
"6052" Or "7467" Or "7740" Or "8020" Or "AE7626" Or "AE7664" Or
"GT7090" Or "GT7216" Or "TA00008",[System exception].[System],[
dbo dtlrecs]. [sysid])
I left joined ([dbodtlrecs].[ id] to [System
exception].[contracts].
Any help is appreciated.

I'm surprised it doesn't give an error message
You need to specify the field for each comparison

IIf([dbodtlrecs].[ id]="3940" Or [dbodtlrecs].[ id]="3994" Or
[dbodtlrecs].[ id]="5205"

Alternatively, you can use the IN operator
IIf([dbodtlrecs].[ id] IN ("3940", "3994", "5205", "6052")

It's a lot easier.to maintain.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
Thank-you very much Bob, for your quick and excellent suggestion. I
used the IN operator and it worked!!!!

Oct 5 '07 #3

P: n/a
fo***********@gmail.com wrote in
news:11*********************@o3g2000hsb.googlegrou ps.com:
On Oct 4, 4:13 pm, Bob Quintal <rquin...@sPAmpatico.cawrote:
>foster.mar...@gmail.com wrote
innews:11**********************@g4g2000hsf.google groups.com:


I have an exception table [System exception] that I want to use
to override the "system" for 11 records and use another table
([dbodtlrecs] for the rest of the records. When I used the
following IIF statement the XTAB query returns the correct
"system" for the exceptions, but the field is blank for the
rest of the records.
System: IIf([dbodtlrecs].[ id]="3940" Or "3994" Or "5205" Or
"6052" Or "7467" Or "7740" Or "8020" Or "AE7626" Or "AE7664" Or
"GT7090" Or "GT7216" Or "TA00008",[System exception].[System],[
dbo dtlrecs]. [sysid])
I left joined ([dbodtlrecs].[ id] to [System
exception].[contracts].
Any help is appreciated.

I'm surprised it doesn't give an error message
You need to specify the field for each comparison

IIf([dbodtlrecs].[ id]="3940" Or [dbodtlrecs].[ id]="3994" Or
[dbodtlrecs].[ id]="5205"

Alternatively, you can use the IN operator
IIf([dbodtlrecs].[ id] IN ("3940", "3994", "5205", "6052")

It's a lot easier.to maintain.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com-
Hide quoted text -

- Show quoted text -

Thank-you very much Bob, for your quick and excellent suggestion.
I used the IN operator and it worked!!!!
As an enhancement to this, the IN statement can be populated from a
table by putting the select query that returns one column inside the
parentheses. If you have a table to hold the exception_Projects, you
could code
IIf([dbodtlrecs].[ id] IN (SELECT ProjectID from Exception_Projects)

It's a lot easier to add a project to a table via a form than to
edit a list of projects in the query editor.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Oct 5 '07 #4

P: n/a
On Oct 5, 10:48 am, Bob Quintal <rquin...@sPAmpatico.cawrote:
foster.mar...@gmail.com wrote innews:11*********************@o3g2000hsb.googlegr oups.com:


On Oct 4, 4:13 pm, Bob Quintal <rquin...@sPAmpatico.cawrote:
foster.mar...@gmail.com wrote
innews:11**********************@g4g2000hsf.googleg roups.com:
I have an exception table [System exception] that I want to use
to override the "system" for 11 records and use another table
([dbodtlrecs] for the rest of the records. When I used the
following IIF statement the XTAB query returns the correct
"system" for the exceptions, but the field is blank for the
rest of the records.
System: IIf([dbodtlrecs].[ id]="3940" Or "3994" Or "5205" Or
"6052" Or "7467" Or "7740" Or "8020" Or "AE7626" Or "AE7664" Or
"GT7090" Or "GT7216" Or "TA00008",[System exception].[System],[
dbo dtlrecs]. [sysid])
I left joined ([dbodtlrecs].[ id] to [System
exception].[contracts].
Any help is appreciated.
I'm surprised it doesn't give an error message
You need to specify the field for each comparison
IIf([dbodtlrecs].[ id]="3940" Or [dbodtlrecs].[ id]="3994" Or
[dbodtlrecs].[ id]="5205"
Alternatively, you can use the IN operator
IIf([dbodtlrecs].[ id] IN ("3940", "3994", "5205", "6052")
It's a lot easier.to maintain.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account fromhttp://www.teranews.com-
Hide quoted text -
- Show quoted text -
Thank-you very much Bob, for your quick and excellent suggestion.
I used the IN operator and it worked!!!!

As an enhancement to this, the IN statement can be populated from a
table by putting the select query that returns one column inside the
parentheses. If you have a table to hold the exception_Projects, you
could code
IIf([dbodtlrecs].[ id] IN (SELECT ProjectID from Exception_Projects)

It's a lot easier to add a project to a table via a form than to
edit a list of projects in the query editor.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
Thanks again, that is very helpful for larger exception tables. I
will give it a try.

Oct 5 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.