var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Querying Dates
I need to query dates such as,
The month of march of 2004
I have the dates set up as 03/15/04.
I can't seem to get it to show me results for March of 2004, for example.
What do I need in the criteria box?
between #3/1/04# and #3/31/04#
I knew it had to be simple. Thank you, I was missing a lb. sign.
It may help you
Try this one.
cc : "The Month of "+MonthName(DatePart('m',Now()))+" of "+CStr(DatePart('yyyy',Now()))
use between #2004-03-01# and #2004-03-31 23:59:59#
What I do is to create a query and add two more fields. Let's say your date fields is called myDate. Add two fields in your query
Use parameters as criteria for the two added fields in your query so that only records. The SQL should look something like this
SELECT Table1.myDate, Month([myDate]) AS [myDateMonth], Year([myDate]) AS [myDateYear]
WHERE (((Month([myDate]))=[MonthWanted]) AND ((Year([myDate]))=[YearWanted]));
cosmos75, that's fine, it works, but unfortunately will not utilize an index on the myDate field
this may not matter in small databases, but in large databases, you want to write your queries in a way that allows indexes to be used
What about using the WHERE condition this way:
WHERE (((Table1.myDate) Between DateSerial([YearWanted],[MonthWanted],1) And DateSerial([YearWanted],[MonthWanted]+1,1)-1));
By utilizing index, you mean to say that the date field here is indexed?
yes, that's it, apply the computations to literals or DATE(), not to the date column