Pulling Records Based on Date Field

[NateUNI]

I have the following query,

select * from bugtracking where
Format([Reported Date],"mm-dd-yyyy&quot = #01-04-2004#

however it does not pull any records, even though there are records for those dates (ie, 1/4/2004 12:29:30 AM) What am I doing wrong with this query, Thanks!

 

[Golom]

Try this

select * from bugtracking where

CDate(

Format([Reported Date],"mm-dd-yyyy")

)

 = #01-04-2004#

 

[cmmrfrds]

I am not sure on the dashes, but the slash should always work.

Format([Reported Date],"mm/dd/yyyy&quot = #01/04/2004#
Or dashes this way.
Format([Reported Date],"yyyy-mm-dd&quot = #2004-01-04#

 

[NateUNI]

Thanks for the replys! However, when I run this query,

select * from bugtracking WHERE
CDate(format([Reported Date],"mm/dd/yyyy&quot) = #01/04/2004#

I recieve the following error:
Data Type Mismatch in Criteria Expression

[Reported Date] is a Date/Time field. So when you convert it to a string with the format statement, then convert it back to a date with the CDate function, that should work. Any ideas what is wrong? Thanks!

 

[dhookom]

I would use the DateValue() function:
select * from bugtracking where
DateValue([Reported Date]) = #01-04-2004#
Converting back and forth between date and text and date doesn't seem optimized.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NateUNI]

When I run the following:

select * from bugtracking where
DateValue([Reported Date]) = #01-04-2004#

I recieve the following error:
Data type mismatch in criteria expression

Any suggestions? Thanks!

 

[dhookom]

What happens when you try:
select * from bugtracking
or
select * from bugtracking where DateValue([Reported Date]) = #01/04/2004#

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NateUNI]

The query, select * from bugtracking
works fine, returns 50,000 rows

The query, select * from bugtracking where DateValue([Reported Date]) = #01/04/2004#
Does not work, I recieved this error: Data type mismatch in criteria expression

The Reported Date field is a type Date/Time with General Date as the format, THANKS!

 

[dhookom]

Have you created your own function "DateValue()"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NateUNI]

No, I'm just running the query in Access, Thanks

Nate

 

[dhookom]

Please view your query in SQL view and copy your SQL statement into a reply/post.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NateUNI]

select * from bugtracking where DateValue([Reported Date]) = #01/04/2004#

 

[dhookom]

If this is giving you an error, I suggest you open any module and attempt to compile all modules. Also check your references (Tools|References) to see if any are listed as MISSING.

Try create a new mdb and import your bugtracking table. Create a new query with exactly the same SQL to see if you have the problem in the new mdb.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[zman22]

#01-04-2004# will return a double representing the date.
The format function returns a string, that is why it does not work, also you have a time part in your date. The following should work:

select * from bugtracking where
fix([Reported Date]) = CDbl(#01-04-2004#)

Fix takes away the time part.

 

[dhookom]

I don't believe NateUNI is using the Format function based on his recent replies. Also, DateValue() will remove the time part of a date/time value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.