How to write an Iif statement with AND function and blank value

[cmorten]

Hi All,

I have been struggling to figure out how to write an Iif statement that includes an AND function. in Layman's term what I want is "IF one field is blank AND another filed contains "DT" then put the words "NOT POSTED" in otherwise put "POSTED"

This is what I have tried with no luck..

IIF(([RECON] IS NULL) AND ([SOURCE]=”DT”),”NOTPOSTED,”POSTED”)

In certain iterations of the formula when I did get it to work, it bracketed the words in quotations and then asked me to enter a parameter value for those words which didn't work and resulted in it returning a blank value. I am better in Excel than I am in Access and can follow intermediate formulas and functions but this one has me stumped.

Any help is appreciated.

[June7]

You are missing quote mark around "NOTPOSTED".

Where are you using this expression - in query?

IS NULL is used in SQL, use IsNull() in textbox or VBA expression.

[cmorten]

Thanks June7!

I am in a Make Table query in Design view and I am trying to add a field in that query that will either show "Posted" or "Not Posted". The original expression I was using was....

Transaction Status: IIf([Recon]="YES","POSTED","NOT POSTED")

This expression worked fine but then we needed make a change in the db.

If am adding the IsNull would the field formula look like...?

Transaction Status: IIF(ISNULL([RECON]) AND ([SOURCE]=”DT”),”NOTPOSTED”,”POSTED”)

[moke123]

Not sure if you can have a space in the field alias - Transaction Status

TransactionStatus: IIF(ISNULL(RECON) AND (SOURCE=”DT”),”NOTPOSTED”,”POSTED”)

[June7]

I do advise not to use spaces nor punctuation/special characters in naming convention.

[cmorten]

Thanks Moke123 and June 7 your help was appreciated. I believe I have figured it out.

I needed to first set the expression with a true/false component and then add in a second IIF expression that would determine what to do based on that outcome. In this case the formula looks like this...

IIf(IIf(IsNull([RECON]) And ([SOURCE]='DT'),1,0)=1,"NOT POSTED","POSTED")

[June7]

That complication should not be necessary. Your original expression should produce the same result. The question is why would it not?