Checking for Null Dates in search criteria

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hello,

I am trying to count how many fields in my table are greater than a certain
date. I have the code below:

-------------------------------------------------
rsTemp!lngJobsAtStartOfWeek = DCount("dteDateCompleted", "tblMain", _
"dteDateCompleted > #" &
Format(DateAdd("d", -1, _
Format(dteTemp, "Medium Date")), "Medium
Date") & "#")
--------------------------------------------------

This counts all completed dates in my main table that are greater than the
date passed in to the procedure (dteTemp). How do I also include fields in
dteDateCompleted that do not contain a date and have just been left blank? I
will be hitting myself when someone tells me the answer! :-)

TIA,

Neil.
 
Count something that is not null, and include Is Null as part of the
criteria:

=DCount("*", "tblMain", "(dteDateCompleted Is Null) OR (dteDateCompleted > "
& Format(dteTemp - 1, "\#mm\/dd\/yyyy\#") & ")")
 
Thanks Allen,

Couple of questions. What is happening now that "*" is used as the criteria.
I thought this had to be the field name for the table? Also, your format
function is hell of a lot better than what I had to do! Does the \/dd\/ mean
that days will be added (or subtracted in this case)?

Thanks again,

Neil.
 
Fair enough.

You must count something that is not Null. The primary key field would work.
However, Access recognises the wildcard and returns a count of all records
without having to check whether the field is Null or not at each row, so
DCount("*", "MyTable") is the fastest way to get an answer (at least in
theory). Of course the 1st argument has no bearing on the criteria: that's
the 3rd argument.

In a JET SQL clause (the 3rd argument is effectively the WHERE clause of a
SQL statement), literal dates need to be presented in the format mm/dd/yyyy.
That's especially important if your users could have a different format such
as the UK's dd/mm/yyyy. The Format() function formats the date for you, but
even if you specify "mm/dd/yyyy" it looks up the separator specified in
control panel and it is is a dot or dash you can finish up with mm.dd.yyyy
or mm-dd-yyyy which is still not right. To avoid this, we must specify that
we want the literal slash character as the seperator. The backslash
character indicates that the following character is a literal, so
"mm\/dd\/yyyy" formats as the month followed by a literal slash and then the
day with another literal slash and then the 4-digit year. Using the same
logic we can specify we want a literal # in the first and last place in the
date, so the format string becomes "\#mm\/dd\/yyyy\#".

The subtracting the date was done with:
dteTemp - 1
though it is probably better to use the DateAdd() expression as you did
originally.

BTW, a simple way to avoid having to type that weird format expression is to
declare a constant in a standard module:
Public strcJetDate = "\#mm\/dd\/yyyy\#"
Then in your code:
Format(dteTemp, strcJetDate)
 
hmm... now why cant they write something this clear in the Access help files
for date format?! :-)

Thanks for clearing that up.

Neil.
 
Back
Top