IIf then Statement for a Date Field

[Bassman]

I've got a fairly simple query. I'm attempting to pull some data based on a "FirstEffectiveDate" and a "CancelDate". What I'm doing is comparing the amount of time an insurance contract has been in-force. Unfortunately, if it is still in-force, the "CancelDate" field is blank/null. What I've tried to do is create an IIf statement that will put in today's date if the field is blank. Unfortunately, what I've tried to use keeps creating an error with the old "Data type mismatch in criteria expression" error. This is the query line I've used:

Cancel Date: IIf([dbo_UWMaster].[Canceldate]="",+Date(),+[dbo_UWMaster].[Canceldate])

I've formatted the field to be a short date so I don't know why I'm getting the error. Please let me know what I"m doing wrong!

Thanks,
Bassman

 

[pbaldy]

try

Nz([Canceldate], Date())

 

[plog]

I understand your explanation, but not your code. Mostly this part:

+Date(), +[dbo_UWMaster].[Canceldate]

What exactly are you hoping this does? Specifically, the + sign. Are you trying to add dates to something? If so, what are these to be added to?

If you want to add dates, use the DateAdd() function (http://www.techonthenet.com/access/functions/date/dateadd.php).
Also, "" does not resolve to Null. To test for a Null value use the IsNull function (http://www.techonthenet.com/access/functions/advanced/isnull.php).

 

[Pat Hartman]

Never format a date except for display. If you simply compare one date field to another, you don't have to worry about regional settings. Once you format a date, it is a string and string comparisons will take place.

Cancel Date: IIf(IsNull([dbo_UWMaster].[Canceldate]), Date(), [dbo_UWMaster].[Canceldate])

ZLS (Zero-length-strings) and Nulls are completely different values. The empty quotes - "" - represent a ZLS. If you want to find out if something is null, you must explicitly check for nulls either with the IsNull() function for VBA or with "Is Null".

I don't know what you were trying to do with the plus signs so I got rid of them.

 

[pbaldy]

Is the IIf() function in conjunction with the IsNull() function more efficient than the Nz() function by itself?

 

[boblarson]

ZLS (Zero-length-strings) and Nulls are completely different values. The empty quotes - "" - represent a ZLS. If you want to find out if something is null, you must explicitly check for nulls either with the IsNull() function for VBA or with "Is Null".

I use Len to check for both ZLS and Nulls in the same statement:

IIf(Len(Nz([FieldName],""))>0, True Part, False Part)

 

[Pat Hartman]

I don't allow ZLS in any of my tables so I don't have that particular problem. In forms, you do have to allow for ZLS because sometimes people use the space bar or backspace and either will result in a ZLS.

Is the IIf() function in conjunction with the IsNull() function more efficient than the Nz() function by itself?

They are not really interchangeable. Nz() will convert null to something else but that won't tell you if the field was null to begin with which is why you would be asking the question. If I want to avoid calculation problems when working with fields that may be null, I use Nz() -
Nz(somefield,0) + Nz(someotherfield,0)

 

[pbaldy]

They are not really interchangeable. Nz() will convert null to something else but that won't tell you if the field was null to begin with which is why you would be asking the question.

While I agree that the functions have different potential uses, in this instance what you posted does functionally the same thing my suggestion did: replace a null field with different field. So my question stands, is the IIf()/IsNull() combination more efficient than Nz() alone?

Also, I don't understand what you mean by Nz() "won't tell you if the field was null to begin with". If the function returns the "ValueIfNull" value, doesn't that tell you the field was Null to begin with?

 

[Pat Hartman]

I wasn't trying to question your answer because it is certainly correct and it is better to use one function than two. I was trying to explain why the poster's solution was wrong. I was just rushing and so failed to mention that I agreed with your solution. Can you forgive me Paul?

 

[pbaldy]

Nothing to forgive! You quoted my post, so I thought I was missing something.