[AccessD] Nz trap with dates in queries

Heenan, Lambert Lambert.Heenan at aig.com
Fri May 3 08:07:37 CDT 2013


Right Jim. I knew about the function argument changing the game. In that circumstance Jet cannot assume the argument is the same every time, so it has to call the function.

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


 Yes JET does.  Unless a function call contains an argument, JET will only do the call once at the start of the query.  It won't repeat it for every row.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Friday, May 03, 2013 08:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [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

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


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



More information about the AccessD mailing list