[AccessD] Nz trap with dates in queries

I think that a golden rule when using Nz(), anywhere, is to *always* convert the result to the data type you expect. Save a lot of confusion.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, May 02, 2013 3:09 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Nz trap with dates in queries

Hi all

I browsed this article:

http://www.fmsinc.com/MicrosoftAccess/query/sort/multiple-dates.htm

and much to my surprise the note about the Nz trap is true.

When used in a query, this expression returns a string:

    DateNotNull: Nz([DateFieldWithNulls], Date())

You can easily see it, because if you apply a date format to the column, it has no effect.

The real strange part, however, is that if you add two other columns:

    TypeDate: VarType([DateFieldWithNulls])
    TypeDatez: VarType(Nz([DateFieldWithNulls], Date()))

the first returns 7 (DateTime) and 1 (Null) while the other returns 7s only ... no 8s for string!

The workaround is either of these methods - the first corrects the result, the second (from the article) uses a good, old alternative:

    DateNotNull: CDate(Nz([DateFieldWithNulls], Date()))
    DateNotNull: IIf([DateFieldWithNulls] Is Null, Date(),
[DateFieldWithNulls])

/gustav

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com