[AccessD] Nz trap with dates in queries

I *think* that when Jet optimizes the query it will figure out that the call to Date() [or Now()] is implicitly looking for a constant value, and so the function call only happens once, at the start of execution of the query. Now if you execute the SQL directly (with CurrentDb.Execute) then that optimization may not happen.

Lambert

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

Hi William

The latter.

You are right about potential issues using Date() this way. I only used it here because it for certain will return a VarType of 7.

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af William Benson
(VBACreations.Com)
Sendt: 2. maj 2013 21:15
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Nz trap with dates in queries

Is the former, or latter faster?

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

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

Also, wouldn't it be better to assign the Date () to a parameter then use that parameter in place of Date()? 

Suppose you ran this at 23:59:00 and it is inside a query which takes a long time to run, wouldn't Date() change for later records?


-----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