Trouble finding Data using criteria

[mattc66 via AccessMonster.com]

I am having trouble and not sure why. The data I am query is a date format.

If I do the following criteria the data result is blank.
Between #1/30/2009# And #1/30/2009#

If I do the below criteria it shows me the data for the 29th including the
30th and the 2nd.
Between #1/29/2009# And #2/2/2009#

What is wrong? I want to be able to show the data based on a certain date. I
don't want to have to select a range of dates just to see the one date I need.


Does anyone know what I am doing wrong?

 

[KARL DEWEY]

You records probably have time associated with the date. Time is recorded as
a decimal fraction and therefore is numerically larger than the date.
the best way to pull these records is to use a calculated field ---
My_Date: CDate([YourDateField])
This removes the decimal time part.

 

[mattc66 via AccessMonster.com]

I formated it as you specified below and it still shows the time.

You records probably have time associated with the date. Time is recorded as
a decimal fraction and therefore is numerically larger than the date.
the best way to pull these records is to use a calculated field ---
My_Date: CDate([YourDateField])
This removes the decimal time part.

I am having trouble and not sure why. The data I am query is a date format.

[quoted text clipped - 9 lines]

Does anyone know what I am doing wrong?

 

[KARL DEWEY]

I thought I remembered the function correctly but this will work --
My_Date: CVDate(Int([YourDateField]))

--
KARL DEWEY
Build a little - Test a little

I formated it as you specified below and it still shows the time.

You records probably have time associated with the date. Time is recorded as
a decimal fraction and therefore is numerically larger than the date.
the best way to pull these records is to use a calculated field ---
My_Date: CDate([YourDateField])
This removes the decimal time part.

I am having trouble and not sure why. The data I am query is a date format.

[quoted text clipped - 9 lines]

Does anyone know what I am doing wrong?

 

[mattc66 via AccessMonster.com]

That worked - thanks for you help.

I thought I remembered the function correctly but this will work --
My_Date: CVDate(Int([YourDateField]))

I formated it as you specified below and it still shows the time.

[quoted text clipped - 8 lines]

 

[John W. Vinson]

I am having trouble and not sure why. The data I am query is a date format.

If I do the following criteria the data result is blank.
Between #1/30/2009# And #1/30/2009#

This will return only records for January 30 at exactly midnight - any value
with a nonzero time portion will be excluded. Karl's suggestion of using
CVdate (or more compactly Datevalue([datefield]) ) will work but will defeat
any indexes on the date field. A more efficient approach is to use a criterion
like

= [Enter start date:] AND < DateAdd("d", 1, [Enter end date:])

If you're only interested in one date, use the same parameter in both
brackets; you'll only be prompted once but it will find all times on that
date.