Easy for Access person...Select By Month 2

[Godfrey]

Hi,

Thank you so much for your help. I am trying to select record for an entire month:

SELECT *
FROM tblTable
WHERE report_date = Format("12/2004","mm/yyyy");

It returns 0 records. There are definitely records for the month of Dec. 2004 in the table.

Table data type for report_date is date/time with a mm/dd/yyyy format.

What am I doing wrong here?

 

[r937]

SELECT *
FROM tblTable
WHERE report_date >= '2004-12-01'
and report_date < '20045-01-01'


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Godfrey]

Thanks so much for your help. Is there anyway to specify by month rather than a range?

-godfrey

 

[PHV]

SELECT *
FROM tblTable
WHERE Format(report_date, "mm/yyyy") = "12/2004";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

yes, you can specify by month as phv showed, but if you have many rows and are interested in performance, the use of a function on the date/time column means that the index on that column, if any, will be ignored, and the database has to do a complete table scan

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Godfrey]

Thank you both for your help!

SELECT *
FROM tblTable
WHERE Format(report_date, "mm/yyyy") = "12/2004";

...Is exactly what I was looking to learn.