Re: need help with IIF statement

On Oct 5, 10:48 am, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
foster.mar...@xxxxxxxxx wrote innews:1191588692.293500.57330@xxxxxxxxxxxxxxxxxxxxxxxxxxx:





On Oct 4, 4:13 pm, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
foster.mar...@xxxxxxxxx wrote
innews:1191533663.695746.121370@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.

.

Relevant Pages

  • Re: need help with IIF statement
    ... ([dbodtlrecs] for the rest of the records. ... following IIF statement the XTAB query returns the correct ... Posted via a free Usenet account fromhttp://www.teranews.com- ... table by putting the select query that returns one column inside the ...
    (comp.databases.ms-access)
  • Re: Can an IIf Statement return more than one value
    ... I am new to VBA but would like to continue to learn more. ... best way to open the query on VBA I am guessng an "On Click" Or 'On Focus" ... appears as the RowSource for your combo box (with the RowSourceType set to ... saying that you want the IIf statement to select multiple rows from the ...
    (microsoft.public.access.formscoding)
  • Re: Find - replace automatic
    ... So there was a quote just before the first IIF statement? ... Microsoft Access MVP ... and ran an Update query against that field. ... you can handle the ApprovalStatus updates ...
    (microsoft.public.access.gettingstarted)
  • Re: Help! Trouble converting values with IIF statement
    ... I ended up trying a different variation within the IIF statement based on ... I'm assuming that "TimingCalc" is a table or query name. ... Time to Arrival (Date diff function) ... I would like to use the following calculation that references Query 1 and ...
    (microsoft.public.access.queries)
  • Re: Need help with a query
    ... You can use the IIf statement in queries: that's an "Immediate If", ... IIf(expr, truepart, falsepart) ... If expr is true, the function returns truepart. ... Is this possible for a query. ...
    (microsoft.public.access.gettingstarted)