[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