if this than that OR if this then that

[dachap1983]

I have two statements that I need to somehow merge together in the same line:

statement #1 is =IIf([type of award]="NA",60,Null)
statement #2 is =IIf([type of award]="NC",90,Null)

So basically looking for something along the lines of this:

=IIf([type of award]="NA",60,Null) OR =IIf([type of award]="NC",90,Null)

Some background if needed:
[type of award] is a combo box
60 and 90 will be used for how many days prior to date entered in another box

both statements work off the same combo box when separated, but the outcomes are in two separate fields instead of one (like I want when the statements are merged)


Thanks.

[pbaldy]

I suspect:

=IIf([type of award]="NA",60, IIf([type of award]="NC",90,Null))

[dachap1983]

perfect! thanks for the help. works like a charm

[pbaldy]

Happy to help!

[dachap1983]

So now my only problem is getting that 60 or 90 to subtract from a date that the user will enter, to determine a "due date" I got it to work in the form, but that's all. Under the query that was created all I get for that expression is #Error, same with when the report is ran.

[pbaldy]

What's the SQL of the query?

[dachap1983]

SELECT Table1.[type of award], Table1.[date due to admin], Table1.[retirement/transfer date], Table1.[NUMBER DAYS DUE], Table1.[date due], IIf([type of award]="NC",90,IIf([type of award]="NA",60,Null)) AS Expr1, DateAdd("d",-[NUMBER DAYS DUE],[retirement/transfer date]) AS Expr2, [date due to admin] AS Expr3
FROM Table1;

I hope that is what you are meaning, I don't use Access in this way. I know the basics and usually just pick and play until it works.

[pbaldy]

Yes, that's what I meant, but I don't see why it would error offhand. Can you post the db here?

[dachap1983]

it says it's to big even after compression, weird as there is nothing even in it.

[pbaldy]

Try doing a compact/repair before zipping.

[dachap1983]

Had to recreate it, wouldn't let me do that after I compressed it through windows

[pbaldy]

The field with the error is this one:

Expr2: DateAdd("d",-[NUMBER DAYS DUE],[retirement/transfer date])

And it errors because there is no value in the [NUMBER DAYS DUE] field. You'll find the symbols and spaces in your field names aren't worth the bother in the long run.

[pbaldy]

I should have added that you can suppress the error:

DateAdd("d",-Nz([NUMBER DAYS DUE],0),[retirement/transfer date])

[dachap1983]

ok, I used what you said and came up with this for the query (it subtracts the 60/90 from the retirement/transfer date):

Expr2: DateAdd("d",-[Expr1],[retirement/transfer date])

everything is good now (I hope, lol). thanks for taking the time to help

[pbaldy]

No problem.