[AccessD] Nz trap with dates in queries

And I just confirmed that the function is only called once.

I created this function

Function myNow() As Date
    Debug.Print "myNow called"
    myNow = Now()
End Function

And then ran this query on a table with 125k rows

SELECT MUR_Data_tbl.[Applicant ID], myNow() AS aDate
FROM MUR_Data_tbl;

And the function was called exactly once.


Then I defined the sub below to open a recordset on that SQL (db.Execute of course is for action queries only), and again the function was called exactly once.

Sub openAQuery()
Dim db As DAO.Database
Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT MUR_Data_tbl.[Applicant ID], myNow() AS aDate FROM MUR_Data_tbl;", dbOpenDynaset)
    rs.MoveLast
    Debug.Print rs.RecordCount
    rs.Close
    Set rs = Nothing
    Set db = Nothing  
End Sub

Lastly I modified the query it include the use of NZ on a field that does have nulls....

SELECT MUR_Data_tbl.[Applicant ID], myNow() AS aDate, nz([MedsEnd],myNow()) AS ME, MUR_Data_tbl.MedsEnd
FROM MUR_Data_tbl;

And still the function is called only once per use. Once for the aDate calculation and once for the use in Nz().

So the question of which is faster below is moot I think.

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

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


Lambert
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Friday, May 03, 2013 8: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