Criteria Date Issue

[mattc66 via AccessMonster.com]

If you could see me know I am sitting here with a handful of hair. I am
pulling it out, trying to figure out what is going on.

I put in the Criteria the #8/31/2006# and it shows no records. The feild is
formated as a Date Fld.

Yet if remove the Criteria I can view all records and see items with dates of
8/31/2006.

What am I doing wrong?

I have another Date Fld next to this one and I can do the same Criteria
without any issues.

 

[Rick Brandt]

If you could see me know I am sitting here with a handful of hair. I
am pulling it out, trying to figure out what is going on.

I put in the Criteria the #8/31/2006# and it shows no records. The
feild is formated as a Date Fld.

Yet if remove the Criteria I can view all records and see items with
dates of 8/31/2006.

What am I doing wrong?

I have another Date Fld next to this one and I can do the same
Criteria without any issues.

Your date values in the table probably include a non-midnight time.
Formatting means nothing here. You have to match the actual stored value
down to the second. That means that using a criteria *range* is much more
practical.

WHERE DateField >= #8/31/2006#
AND DateField < #9/1/2006#

 

[mattc66 via AccessMonster.com]

Okay that works, but sucks.. I wanted to allow the user to enter the start
date and the end date of the range. Now I have to have them enter the start
date and the date after the end date to get the results.

If you could see me know I am sitting here with a handful of hair. I
am pulling it out, trying to figure out what is going on.

[quoted text clipped - 9 lines]

I have another Date Fld next to this one and I can do the same
Criteria without any issues.

Your date values in the table probably include a non-midnight time.
Formatting means nothing here. You have to match the actual stored value
down to the second. That means that using a criteria *range* is much more
practical.

WHERE DateField >= #8/31/2006#
AND DateField < #9/1/2006#

 

[Greg]

You can use a "less than or equal to" as well as a "greater than or equal
to" comparison operator, giving:

WHERE DateField >= #8/31/2006# AND DateField <= #9/1/2006#

Greg

Okay that works, but sucks.. I wanted to allow the user to enter the start
date and the end date of the range. Now I have to have them enter the
start
date and the date after the end date to get the results.

If you could see me know I am sitting here with a handful of hair. I
am pulling it out, trying to figure out what is going on.

[quoted text clipped - 9 lines]

I have another Date Fld next to this one and I can do the same
Criteria without any issues.

Your date values in the table probably include a non-midnight time.
Formatting means nothing here. You have to match the actual stored value
down to the second. That means that using a criteria *range* is much more
practical.

WHERE DateField >= #8/31/2006#
AND DateField < #9/1/2006#

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[Rick Brandt]

Okay that works, but sucks.. I wanted to allow the user to enter the start
date and the end date of the range. Now I have to have them enter the start
date and the date after the end date to get the results.

No you don't.

SELECT *
FROM TableName
WHERE DateField >= [Enter Start Date]
AND DateField < DateAdd("d", 1, [Enter End Date])

 

[John Spencer]

Let the user enter the end date and then in the query add 1 day to the end
date. In a parameter prompt that would look like the following.

WHERE DateField >= CDate([Enter Start Date]) AND DateField <
DateAdd("d",1,CDate([Enter End Date]))

Okay that works, but sucks.. I wanted to allow the user to enter the start
date and the end date of the range. Now I have to have them enter the
start
date and the date after the end date to get the results.

If you could see me know I am sitting here with a handful of hair. I
am pulling it out, trying to figure out what is going on.

[quoted text clipped - 9 lines]

I have another Date Fld next to this one and I can do the same
Criteria without any issues.

Your date values in the table probably include a non-midnight time.
Formatting means nothing here. You have to match the actual stored value
down to the second. That means that using a criteria *range* is much more
practical.

WHERE DateField >= #8/31/2006#
AND DateField < #9/1/2006#

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com