Dates have to be delimited with # symbols, and must be in mm/dd/yyyy format,
regardless of what your Regional Settings are. (Okay, that's not strictly
true: you can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy.
The point is, you cannot use dd/mm/yyyy in a DLookup even if that's what
your Regional Settings are)
I tend to use the Format statement to do both of the points above:
stTermID = DLookup("[TermID]", "zTerms", "[TermStartDate] < " &
Format(stIncidentDate, "\#mm\/dd\/yyyy\#") & " And [TermEndDate]>" &
Format(stIncidentDate "\#mm\/dd\/yyyy\#"))
If that still doesn't work, are you certain there is a value within the
range? Do stIncidentDate also include time?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Colin Mardell" <co***********@btopenworld.com> wrote in message
news:e9*************************@posting.google.co m...
Can anyone tell me why this VBA returns a null value when the source
table doesn't have one?
I have a table called 'zTerms' with fields 'TermID', 'Term',
'TermStartDate' and 'TermEndDate'
I need to return the TermID when the IncidentDate shown on the form is
between the the two dates.
I have tried the following methods all to no avail.
stTermID = DLookup("[TermID]", "zTerms", "[TermStartDate] < " &
stIncidentDate & " And [TermEndDate]>" & stIncidentDate)
stTermID = DLookup("[TermID]", "zTerms", "[TermStartDate] < " &
stIncidentDate & " And [TermEndDate]>#" & stIncidentDate & "#")
Help!!!
