IIf Date IsNull DateSerial #Error 1

[wvmbark]

I've got 3 fields in a query: [FAE] has a 1 or 0 in it; [CertDate] contains a date if the employee is certified, otherwise it is null; [Qualified] is a calculated field that should return "1" IF [FAE] is "1" AND IF the last day of the month 12 months from [CertDate] is > the last day of current month.

This expression...

Qualified: IIf([FAE]=1 And DateSerial(Year([CertDate]),Month([CertDate])+12,0)>DateSerial(Year(Date()),Month(Date())+1,0),1,0)

...works except if [FAE] is "1" and [CertDate] is null, it returns "#Error".

Could somebody please help me out before my brain melts??

Thank you!!!

 

[PHV]

You may try this:
Qualified: IIf([FAE]=1 And DateSerial(Year(Nz([CertDate],Date()),Month(Nz([CertDate],Date())+13,0)>DateSerial(Year(Date()),Month(Date())+1,0),1,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wvmbark]

PH,

No errors, but wrong value - it always returns a "1" if [CertDate] is null.

If I remove the [valueifnull] part of the Nz statement or change it to a zero, it works - I get a zero. I really don't understand the implications of not using it. Do I need to use the [valueifnull] here?

Thank you!!!

 

[wvmbark]

I also have a caculated field [ExpDate] that uses...

ExpDate: DateSerial(Year([CertDate]),Month([CertDate])+13,0)

...to return the last day of the month 12 months from [CertDate]. It works when then is a [CertDate], but if not I get the "#Error". I can't seem to get the Nz function or an IIf statement to work here.

Any sugestions would be much appreciated!

 

[PHV]

Qualified: IIf(IsNull([CertDate],0,IIf([FAE]=1 And DateSerial(Year(Nz([CertDate],Date()),Month(Nz([CertDate],Date())+13,0)>DateSerial(Year(Date()),Month(Date())+1,0),1,0))

ExpDate: IIf(IsNull([CertDate],Null,DateSerial(Year([CertDate]),Month([CertDate])+13,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wvmbark]

PHV,

In the Qualified expression: Why IsNull in the part and Nz in the second? And are the Nz's necessary in the falsepart since the first truepart returns "0" if [CertDate] is null?

I've entered like this...

Qualified: IIf(IsNull([CATCertDate]),0,IIf([FAE]=1 And DateSerial(Year([CertDate]),Month([CertDate]),+13,0)>DateSerial(Year(Date()),Month(Date())+1,0),1,0))

... but it gives me an warning saying the expression contains a function with the wrong number of arguments. Can the nested IIf be in the falsepart? Any ideas??

Thanks!

 

[wvmbark]

The extra comma before +13 was the problem...

Works great now, thanks!!