Dates in queries

[Gargamil]

Greetings

I am trying to search a table of about 17,000 records for combination
dates and equipment numbers in VBA. I am trying to debug the code because
the lookups only return the first of the records against a given EqNum. It
seems the date part of the function is ignored in the code. Granted the
code is not fully formed but what am I missing here??

The dates are in the table are in Australian format dd/mm/yyyy - hence
the format function below - although that did not seem to solve the problem.

Function LookupMeterReading() '(EqNum, SampleDate)

EqNum = "714-R/HFDRV"
SampleDate = "7/11/2006"

Debug.Print DateValue(SampleDate), Format(SampleDate, "mm/dd/yyyy")

criteria = "([Date] = #" & DateValue(SampleDate) & "#) and ([EqNum] =
'" & EqNum & "')"

MeterRead = DLookup("[MeterReading]", "HSOILSMP", criteria)
AlarmGrop = DLookup("[AlarmGroupID]", "HSOILSMP", criteria)
LoadDate = DLookup("[LoadDate]", "HSOILSMP", criteria)

Debug.Print MeterRead, AlarmGrop, LoadDate, criteria

End Function

 

[Douglas J. Steele]

You must use mm/dd/yyyy format (or an unambiguous one, such as yyyy-mm-dd or
dd mmm yyyy), regardless of your Regional Settings:

criteria = "([Date] = " & Format(DateValue(SampleDate, "\#mm\/dd\/yyyy\#")
& _
" And ([EqNum] = '" & EqNum & "')"

For more details, check Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html ,or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Graham Mandeno]

Hi Gargamil

You have a misconception about the way dates are stored. You say, "the
dates are in the table are in Australian format dd/mm/yyyy", but (assuming
this is a date/time field and not a text field) the date is simply stored as
a single number, and they are *displayed* in the dd/mm/yyyy format because
of your regional settings and/or your format settings.

Now, when you convert a string to a date in VBA your regional settings are
used to interpret the format.

So, Format(DateValue("7/11/2006"), "d mmm yyyy") will return:
"7 Nov 2006" if you are in Australia/NZ/UK
or "11 Jul 2006" if you are in the USA

Similarly, when you convert a date to a string in VBA, your "short date"
regional setting is used for the format.

So Debug.Print DateValue("7-Nov-2006") will display:
"7/11/2006" if you are in Australia/NZ/UK
or "11/7/2006" if you are in the USA

HOWEVER, SQL pays no attention to regional settings, and any date in the
form xx/xx/xxxx is interpreted as mm/dd/yyyy.

So you need to format the date appropriately in your SQL string:

criteria = "([Date] = " & Format(SampleDate, "\#mm\/dd\/yyyy\#") & ") ..."

(Note that the "\/" is a backslash/forwardslash, which ensures that a slash
is used instead of your regional date separator, which may be a different
character.)

This is such a common requirement that I have a general-purpose function to
do it:

Public Function SQLDate( dt as Date ) as String
SQLDate = Format(dt, "\#mm\/dd\/yyyy\#")
End Function

Now you can just say:
criteria = "([Date] = " & SQLDate(SampleDate) & ") ..."

 

[Gargamil]

Hello, Douglas!

Thanks for the response. I still get the first result returned by the
query. The date is formatted as a date in the table and yet it is being
ignored by the query. What else could I be missing?
The result of the debug.print is below to confirm the criteria
expression.

2496 B78X-FDRVDIF 20/02/2001 ([Date] = #11/07/2006#) And
([EqNum] = '714-R/HFDRV')

g

You wrote in conference microsoft.public.access on Thu, 23 Nov 2006
17:30:30 -0500:

DJS> criteria = "([Date] = " & Format(DateValue(SampleDate,
DJS> "\#mm\/dd\/yyyy\#") & _
DJS> " And ([EqNum] = '" & EqNum & "')"

DJS> For more details, check Allen Browne's "International Dates in Access"
DJS> at http://allenbrowne.com/ser-36.html ,or what I have in my September
2003
DJS> Access Answers column for Pinnacle Publication's "Smart Access"
DJS> newsletter. (The column and accompanying database can be downloaded
DJS> for free at http://www.accessmvp.com/djsteele/SmartAccess.html)

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no private e-mails, please)

DJS> ??>> Greetings
??>>
??>> I am trying to search a table of about 17,000 records for
??>> combination dates and equipment numbers in VBA. I am trying to debug
??>> the code because the lookups only return the first of the records
??>> against a given EqNum. It seems the date part of the function is
??>> ignored in the code. Granted the code is not fully formed but what am
??>> I missing here??
??>>
??>> The dates are in the table are in Australian format dd/mm/yyyy -
??>> hence the format function below - although that did not seem to solve
??>> the problem.
??>>
??>> Function LookupMeterReading() '(EqNum, SampleDate)
??>>
??>> EqNum = "714-R/HFDRV"
??>> SampleDate = "7/11/2006"
??>>
??>> Debug.Print DateValue(SampleDate), Format(SampleDate, "mm/dd/yyyy")
??>>
??>> criteria = "([Date] = #" & DateValue(SampleDate) & "#) and
??>> ([EqNum] = '" & EqNum & "')"
??>>
??>> MeterRead = DLookup("[MeterReading]", "HSOILSMP", criteria)
??>> AlarmGrop = DLookup("[AlarmGroupID]", "HSOILSMP", criteria)
??>> LoadDate = DLookup("[LoadDate]", "HSOILSMP", criteria)
??>>
??>> Debug.Print MeterRead, AlarmGrop, LoadDate, criteria
??>>
??>> End Function
??>>

With best regards, Gargamil. E-mail: (e-mail address removed)

 

[Douglas J. Steele]

Does the date field also have a time associated with it (as it would it,
say, you used the Now() function to populate it)?

Try:


criteria = "(DateValue([Date]) = " & _
Format(DateValue(SampleDate,"\#mm\/dd\/yyyy\#") & _
" And ([EqNum] = '" & EqNum & "')"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello, Douglas!

Thanks for the response. I still get the first result returned by the
query. The date is formatted as a date in the table and yet it is being
ignored by the query. What else could I be missing?
The result of the debug.print is below to confirm the criteria
expression.

2496 B78X-FDRVDIF 20/02/2001 ([Date] = #11/07/2006#) And
([EqNum] = '714-R/HFDRV')

g

You wrote in conference microsoft.public.access on Thu, 23 Nov 2006
17:30:30 -0500:

DJS> criteria = "([Date] = " & Format(DateValue(SampleDate,
DJS> "\#mm\/dd\/yyyy\#") & _
DJS> " And ([EqNum] = '" & EqNum & "')"

DJS> For more details, check Allen Browne's "International Dates in
Access"
DJS> at http://allenbrowne.com/ser-36.html ,or what I have in my September
2003
DJS> Access Answers column for Pinnacle Publication's "Smart Access"
DJS> newsletter. (The column and accompanying database can be downloaded
DJS> for free at http://www.accessmvp.com/djsteele/SmartAccess.html)

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no private e-mails, please)

DJS> ??>> Greetings
??>>
??>> I am trying to search a table of about 17,000 records for
??>> combination dates and equipment numbers in VBA. I am trying to debug
??>> the code because the lookups only return the first of the records
??>> against a given EqNum. It seems the date part of the function is
??>> ignored in the code. Granted the code is not fully formed but what
am
??>> I missing here??
??>>
??>> The dates are in the table are in Australian format dd/mm/yyyy -
??>> hence the format function below - although that did not seem to solve
??>> the problem.
??>>
??>> Function LookupMeterReading() '(EqNum, SampleDate)
??>>
??>> EqNum = "714-R/HFDRV"
??>> SampleDate = "7/11/2006"
??>>
??>> Debug.Print DateValue(SampleDate), Format(SampleDate,
"mm/dd/yyyy")
??>>
??>> criteria = "([Date] = #" & DateValue(SampleDate) & "#) and
??>> ([EqNum] = '" & EqNum & "')"
??>>
??>> MeterRead = DLookup("[MeterReading]", "HSOILSMP", criteria)
??>> AlarmGrop = DLookup("[AlarmGroupID]", "HSOILSMP", criteria)
??>> LoadDate = DLookup("[LoadDate]", "HSOILSMP", criteria)
??>>
??>> Debug.Print MeterRead, AlarmGrop, LoadDate, criteria
??>>
??>> End Function
??>>

With best regards, Gargamil. E-mail: (e-mail address removed)

 

[Graham Mandeno]

Hi Gargamil

Are you sure you are getting the wrong record? If DLookup doesn't find a
record that matches the criteria then it returns Null, not the field from
the first record.

In your Debug.Print I see a date of 20/02/2001, but that is not the field in
your criteria string, so can you be certain that it is not a match?

In any case, doing three DLookups on the same data source with the same
criteria is VERY inefficient. I suggest you open a recordset instead:

Dim rs as DAO.Recordset
Dim criteria as String
' temporary variables for testing
Dim SampleDate as String
Dim EqNum as String

EqNum = "714-R/HFDRV"
SampleDate = "7/11/2006"

criteria = "([Date] = " & Format(SampleDate,"\#mm\/dd\/yyyy\#") _
& ") and ([EqNum] = '" & EqNum & "')"
Set rs = CurrentDb.OpenRecordset("Select * from HSOILSMP where " _
& criteria, dbOpenSnapshot)
With rs
Do Until .EOF
Debug.Print !Date, !EqNum, !MeterReading, !AlarmGroupID, !LoadDate
.MoveNext
Loop
.Close
End With

This will print all the relevant fields from all the records matching the
criteria and might give you some clue as to what is going on.

I notice that you are not declaring variables. This is Bad Practice! Every
module should have Option Explicit at the top of it, and every variable you
use should be declared as I have done above, with the Dim statements.
Otherwise a simple typo can cause problems that can take hours (or more!) to
trace.

For example, if you accidentally typed:
crteria = ... (missing an "i")
and then used criteria (with the "i") in your DLookups, then criteria would
be uninitialised and would therefore be null. That would certainly explain
getting the first record in your data source!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello, Douglas!

Thanks for the response. I still get the first result returned by the
query. The date is formatted as a date in the table and yet it is being
ignored by the query. What else could I be missing?
The result of the debug.print is below to confirm the criteria
expression.

2496 B78X-FDRVDIF 20/02/2001 ([Date] = #11/07/2006#) And
([EqNum] = '714-R/HFDRV')

g

You wrote in conference microsoft.public.access on Thu, 23 Nov 2006
17:30:30 -0500:

DJS> criteria = "([Date] = " & Format(DateValue(SampleDate,
DJS> "\#mm\/dd\/yyyy\#") & _
DJS> " And ([EqNum] = '" & EqNum & "')"

DJS> For more details, check Allen Browne's "International Dates in
Access"
DJS> at http://allenbrowne.com/ser-36.html ,or what I have in my September
2003
DJS> Access Answers column for Pinnacle Publication's "Smart Access"
DJS> newsletter. (The column and accompanying database can be downloaded
DJS> for free at http://www.accessmvp.com/djsteele/SmartAccess.html)

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no private e-mails, please)

DJS> ??>> Greetings
??>>
??>> I am trying to search a table of about 17,000 records for
??>> combination dates and equipment numbers in VBA. I am trying to debug
??>> the code because the lookups only return the first of the records
??>> against a given EqNum. It seems the date part of the function is
??>> ignored in the code. Granted the code is not fully formed but what
am
??>> I missing here??
??>>
??>> The dates are in the table are in Australian format dd/mm/yyyy -
??>> hence the format function below - although that did not seem to solve
??>> the problem.
??>>
??>> Function LookupMeterReading() '(EqNum, SampleDate)
??>>
??>> EqNum = "714-R/HFDRV"
??>> SampleDate = "7/11/2006"
??>>
??>> Debug.Print DateValue(SampleDate), Format(SampleDate,
"mm/dd/yyyy")
??>>
??>> criteria = "([Date] = #" & DateValue(SampleDate) & "#) and
??>> ([EqNum] = '" & EqNum & "')"
??>>
??>> MeterRead = DLookup("[MeterReading]", "HSOILSMP", criteria)
??>> AlarmGrop = DLookup("[AlarmGroupID]", "HSOILSMP", criteria)
??>> LoadDate = DLookup("[LoadDate]", "HSOILSMP", criteria)
??>>
??>> Debug.Print MeterRead, AlarmGrop, LoadDate, criteria
??>>
??>> End Function
??>>

With best regards, Gargamil. E-mail: (e-mail address removed)