Dealing with blanks

[Precips1]

Hello People,

I'm in need of help, just can't get my head around why this doesn't work?

I'm dealing with 3 or 4 fields names, Indate: [ExpireDate], [CurrentDocVer], [TrainedVerNo]

The idea is: If the [Expiredate] is > than today then the named field Indate: replies "Expired" else "Live" also, if [CurrentDocVer] > [TrainedVerNo] replies "Expired" else "Live"

So in the field named Indate:, I have this and it seems to work fine:-

Indate: IIf([ExpireDate]>Date(),IIf([CurrentDocVer]>[TrainedVerNo],"Expired","Live"))

Two things. although this works fine with data fields that have data in, I can't seem to get it to handle blanks. eg if [ExpireDate] has no date or [TrainedVerNo] is empty.

Let me try to explain a little more; [ExpireDate] is calculated from a data field called [RegistrationDate] in other words, its the date the person was trained. If this field is blank ie person not trained yet, then [ExpireDate] is also blank.

Therefore, in my world I'd like to add this field to the line of code to say if RegistrationDate is blank then Indate = "Expired" else "Live"

Indate: IIf([ExpireDate]>Date(),IIf([CurrentDocVer]>[TrainedVerNo],IIf ([RegistrationDate]="","Expired","Live")))

This turns out to be rubbish! This line then blanks out all the data fields that has data and just fills in the ones that are blank. I've also tried Nz in front of things

Indate: IIf(Nz([ExpireDate]>Date()),IIf(Nz([CurrentDocVer]>[TrainedVerNo]),IIf (Nz([RegistrationDate]=""),"Expired","Live")))

Also, looked at Is Null and get the same sort of rubbish reply's.

Please help, its been days on this now and I'm slowly loosing the will to live.

Regards,
Mike

 

[Mihail]

Take a closer look to the NZ function: it has TWO arguments.

 

[Precips1]

Mihail, thanks for the speedy reply.

I have looked closely,

What's wrong with this?

Indate: IIf(Nz([ExpireDate]>Date()),IIf(Nz([CurrentDocVer]>[TrainedVerNo]),IIf (Nz([RegistrationDate]=""),"Expired","Live")))

 

[Mihail]

From Access help file:
Syntax
expression.Nz(Value, ValueIfNull)
So the function return the Value if Value <> Null and return ValueIfNull if Value = Null.
You don't say to the NZ function what to return if Value is Null.

NZ([ExpireDate]) should be NZ([ExpireDate],0)
I assume that, if the [ExpireDate] is Null you wish to obtain ZERO.


Other example:
NZ([ExpireDate],Date())
This time the NZ function will return the current date when the [ExpireDate] is null.