Help with DLookup!

[Yes2man]

I am using DLookup to find date fields in a table. One date field is
formatted as short date (i.e. 01/01/1900) and the other is in general date
format (i.e. 01/01/1900 10:00:00 PM) but has user entries that both have the
time and don't have the time. I have tried the following code:

DLookup("[TSI]", "Calendar of Events: All Events", "[Begin Date] = #" &
startdate1 & "# AND [TSI] = '" & rndtsi & "'")

where startdate1 represents a variable (a date) based on user input (short
date format) and rndtsi is a variable representing the name of the individual
I am looking for. The code works fine when there is an exact match between
[Begin Date] and startdate1 (when both are in the short date format), but
when the entry in [Begin Date] is in general date format (has the time),
DLookup misses it! I've tried to use Format and also tried using the 'Like'
operator in the DLookup, but I can't get it right.

Any help would be greatly appreciated!

 

[Klatuu]

First point is that date data type fields are not formatted as anything.
They are stored as floating point decimal numbers. Formatting occurs only
when the field value is retrieved for evaluation or presentation.

The issue you have is a mixture of date fields with and without time. The
DLookup is looking for an exact match, so two values for the same date may
not compare as equal if either or both have times and the times are not exact
to the second. The solution is to use the DateValue function. It returns
only the date portion of the value, so you can use it on both sides of the
equasion when you want to compare dates regardless of time.

DLookup("[TSI]", "Calendar of Events: All Events", "DateValue([Begin Date])
= #" &

DateValue(startdate1) & "# AND [TSI] = '" & rndtsi & "'")

--
Dave Hargis, Microsoft Access MVP

I am using DLookup to find date fields in a table. One date field is
formatted as short date (i.e. 01/01/1900) and the other is in general date
format (i.e. 01/01/1900 10:00:00 PM) but has user entries that both have the
time and don't have the time. I have tried the following code:

DLookup("[TSI]", "Calendar of Events: All Events", "[Begin Date] = #" &
startdate1 & "# AND [TSI] = '" & rndtsi & "'")

where startdate1 represents a variable (a date) based on user input (short
date format) and rndtsi is a variable representing the name of the individual
I am looking for. The code works fine when there is an exact match between
[Begin Date] and startdate1 (when both are in the short date format), but
when the entry in [Begin Date] is in general date format (has the time),
DLookup misses it! I've tried to use Format and also tried using the 'Like'
operator in the DLookup, but I can't get it right.

Any help would be greatly appreciated!

 

[Yes2man]

Outstanding! Thanks for the help and the quick turnaround...problem solved!
--
Yes2man

First point is that date data type fields are not formatted as anything.
They are stored as floating point decimal numbers. Formatting occurs only
when the field value is retrieved for evaluation or presentation.

The issue you have is a mixture of date fields with and without time. The
DLookup is looking for an exact match, so two values for the same date may
not compare as equal if either or both have times and the times are not exact
to the second. The solution is to use the DateValue function. It returns
only the date portion of the value, so you can use it on both sides of the
equasion when you want to compare dates regardless of time.

DLookup("[TSI]", "Calendar of Events: All Events", "DateValue([Begin Date])
= #" &

DateValue(startdate1) & "# AND [TSI] = '" & rndtsi & "'")

--
Dave Hargis, Microsoft Access MVP

I am using DLookup to find date fields in a table. One date field is
formatted as short date (i.e. 01/01/1900) and the other is in general date
format (i.e. 01/01/1900 10:00:00 PM) but has user entries that both have the
time and don't have the time. I have tried the following code:

DLookup("[TSI]", "Calendar of Events: All Events", "[Begin Date] = #" &
startdate1 & "# AND [TSI] = '" & rndtsi & "'")

where startdate1 represents a variable (a date) based on user input (short
date format) and rndtsi is a variable representing the name of the individual
I am looking for. The code works fine when there is an exact match between
[Begin Date] and startdate1 (when both are in the short date format), but
when the entry in [Begin Date] is in general date format (has the time),
DLookup misses it! I've tried to use Format and also tried using the 'Like'
operator in the DLookup, but I can't get it right.

Any help would be greatly appreciated!

 

[Klatuu]

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

Outstanding! Thanks for the help and the quick turnaround...problem solved!
--
Yes2man

First point is that date data type fields are not formatted as anything.
They are stored as floating point decimal numbers. Formatting occurs only
when the field value is retrieved for evaluation or presentation.

The issue you have is a mixture of date fields with and without time. The
DLookup is looking for an exact match, so two values for the same date may
not compare as equal if either or both have times and the times are not exact
to the second. The solution is to use the DateValue function. It returns
only the date portion of the value, so you can use it on both sides of the
equasion when you want to compare dates regardless of time.

DLookup("[TSI]", "Calendar of Events: All Events", "DateValue([Begin Date])
= #" &

DateValue(startdate1) & "# AND [TSI] = '" & rndtsi & "'")

--
Dave Hargis, Microsoft Access MVP

I am using DLookup to find date fields in a table. One date field is
formatted as short date (i.e. 01/01/1900) and the other is in general date
format (i.e. 01/01/1900 10:00:00 PM) but has user entries that both have the
time and don't have the time. I have tried the following code:

DLookup("[TSI]", "Calendar of Events: All Events", "[Begin Date] = #" &
startdate1 & "# AND [TSI] = '" & rndtsi & "'")

where startdate1 represents a variable (a date) based on user input (short
date format) and rndtsi is a variable representing the name of the individual
I am looking for. The code works fine when there is an exact match between
[Begin Date] and startdate1 (when both are in the short date format), but
when the entry in [Begin Date] is in general date format (has the time),
DLookup misses it! I've tried to use Format and also tried using the 'Like'
operator in the DLookup, but I can't get it right.

Any help would be greatly appreciated!