IIf function in a query

[WAVP375]

A query containing two fields, [CertificationExpiration] and [LifeTimeCertification] where only one of the fields will contain data and the other will be blank. In a query I am attempting to create a field called CertDate: which will contain the content of which ever field is populated. I am using the following statement. I get the false value but cannot get the true value.



CertDate: IIf([LifeTimeCertification]=Null,[CertificationExpiration],[LifeTimeCertification])

I have turn the statement around and interigated the CertificationExpiration field and get the same results, no value when true.

What am I overlooking

Thanks

[ranman256]

you dont = NULL, you must use a function

IIf([ISNULL(LifeTimeCertification]),[CertificationExpiration],[LifeTimeCertification])

[WAVP375]

I copied and pasted you expression and i get an error saying 'wrong number of arguements"

[ArviLaanemets]

CertDate: IIf([LifeTimeCertification]=Null,[CertificationExpiration],[LifeTimeCertification])

CertDate: IIf(Nz([LifeTimeCertification],0)=0,[CertificationExpiration],[LifeTimeCertification])

[WAVP375]

Both true and false returns #ERROR

[June7]

Ranman has a typo with misplaced [ in front of ISNULL.

IIf(ISNULL([LifeTimeCertification]),[CertificationExpiration],[LifeTimeCertification])

Simpler structure:
Nz([LifeTimeCertification], [CertificationExpiration])

However, I see no reason for cause of #ERROR in the already suggested Nz expression as syntax is correct, so whatever the cause, will probably occur for any of the suggestions.

Review: http://allenbrowne.com/QueryPerfIssue.html

This calc can also be done in textbox on form or report.

An alternative table structure would have a single field for date and another field (could be Yes/No) to flag the date as a lifetime certification.

[ssanfu]

Just curious.... what do you do if/when both are Null??? I don't yet have a certification, nor a LifeTimeCertification...

[WAVP375]

Thanks guys, I got it worked out. Fortunatly there is always more than one way to skin a cat.