Nz Function for Access VBA DATES

[NotAnExpert]

Hi, having sorted my previous issue with nz, I am now stuck trying a similar approach to dates.

I have my mainform, and a subform is updated with a button onclick event.

My mainform contains two textboxes which are formatted for dates and has datepickers etc.

My query runs ok using NZ but as before, I want this working via VBA and i'm not entirely sure why this isn't working.

The query SQL view is as follows and the subform running this query WORKS FINE:

((qryTransactions.[Del dt])>=Nz([forms]![frmHistorical]![calDateFrom],"01/01/1901") And (qryTransactions.[Del dt])<=Nz([forms]![frmHistorical]![CalDateTo],Date()))

Any assistance getting the syntax right on th VBA SQL version on this would be awesome.

Thank you again.

 

[theDBguy]

Hi. I'm actually surprised that works. I usually use the CDate() or the Format() function to make sure the input gets interpreted as a date value.

Sent from phone...

 

[arnelgp]

use 0 or 1, the Date you are seing in GUI is but a representation of it's Internal numeric Double value:

((qryTransactions.[Del dt])>=Nz([forms]![frmHistorical]![calDateFrom], 1) And (qryTransactions.[Del dt])<=Nz([forms]![frmHistorical]![CalDateTo],Date()))

 

[namliam]

Any assistance getting the syntax right on th VBA SQL version on this would be awesome.

If you are working in VBA, when your form value is NULL you want to OMIT the where clause all together not "force" it to be true using NZ.
The SQL solution is a poor mans way of solving a very simple problem.

More over your SQL is comparing strings and Dates, resulting in Implicit conversion one way or the other, which can result in strange unexpected results... if not NOW then tomorrow or at some time when you absolutely do not need it.

myWhere = "where 1=1"
if [forms]![frmHistorical]![calDateFrom] is not null then
    myWhere = myWhere & " AND [qryTransactions.[Del dt]>=#" & [forms]![frmHistorical]![calDateFrom] & "#"
end if
If [forms]![frmHistorical]![CalDateTo] is not null then
    myWhere = myWhere & " And qryTransactions.[Del dt]<=#" & [forms]![frmHistorical]![CalDateTo] & "#"
ELSE '*0
    myWhere = myWhere & " And qryTransactions.[Del dt]<= Date() " '*1
end if

*0
The ELSE may not be needed if Del dt has no records in the future... depending on the requirement in *1

*1
Note that Del dt could contain a time component as well and can result in unexpected results for Today + 1 minute > Today
You may want to consider using NOW() instead, unless you want to explicitly exclude today.

You already know all the below but I always re-itterate this when I see it....

Note 1:
For this to work properly the calDateFrom and CalDateTo MUST be intered in US format, i.e. 09/30/2020...
Access is smart enough to make 30/09/2020 into 09/30/2020 as "month" 30 does not exist. But come tomorrow 1/10/2020 may get translated into Jan 10, 2020.
If them fields on your form are datepickers, not text fields you may want to Format(field, "MM/DD/YYYY") to be sure of the format.

Note 2:
Naming conventions, stick to them calDateFrom vs CalDateTo is not sticking to one.

Note 3:
[Del dt], is a bad field name, you are ill adviced using spaces or special characters in field names... als it makes it sound like the record is "deleted" where your where clause makes it sound like it is more like "calDate".
And here lies a second naming thingy, Del dt vs calDate is 4 different things....
Either
Del dt and delDtFrom
cal dt and calDtFrom
DelDate and delDateFrom
calDate and calDateFrom
Keeping things the same makes things easier to follow and support in the future.

 

[NotAnExpert]

As always, excellent responses, thank you very much!