Full Version: IIF function and dates
UtterAccess Forums > Microsoft® Access > Access Date + Time
garcanrya
Hi,
I have two date fields and I need to pick up the earlier one.
I'm working with an old patient record (not my own design), and I need to get the ending dates of patient's visits. The problem is that sometimes the ending date (DateOfDischarge) has (incorrectly) not been filled in. Sometimes the reason for this is that the patient has died, which means he has a DateOfDeath field filled.
I tried the following line as part of the SQL query:
IIf([Perus]![DateOfDischarge]=Null,[Perus]![DateOfDeath],[Perus]![DateOfDischarg
e]) AS EndDate
but for some reason DateOfDischarge newer seems to "trigger" the null, since (even though there are empty fields in the DateOfDischarge column) the DateOfDeath newer ends up to the EndDate column.
What might be the problem?
Thanks in advance,
garcanrya
vtd
You cannot test anything equal to Null. You need to use the IsNull() function to test for Null like:
If(IsNull([Perus]![DateOfDischarge]),[Perus]![DateOfDeath],[Perus]![DateOfDisch
arge]) AS EndDate
gideon
in VBA rather use
isnull([Perus]![DateOfDischarge])
the = null returns false info.
IN SQL use the
([Perus]![DateOfDischarge]) Is Not Null
truittb
Instead of the IIf() use Nz()
z([Perus]![DateOfDischarge],[Perus]![DateOfDeath]) AS EndDate
SerranoG
Try the Nz function instead of the IIF,
z([Perus]![DateOfDischarge], [Perus]![DateOfDeath]) As EndDate
garcanrya
Thank you *most warmly* for the advice, all of you - how elementary my mistake was. Works perfectly now!
Thanks!
- garcanrya
jsitraining
Quick point gideon,
=Null does not return false it returns Null.
If it returned false the you would be able to get a true by negating the false :
<> Null
this does not happen.
A subtle but important distinction.
Jim
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.