Dates

G

Greg

I am trying to do a select query on one table.
This table has many records with a DATE type field in each record.
I would like to select all records from the table with a given month and
year, the day is not important in this query.
I would like to make this a "parameter" driven query. My intention is to be
able to enter Month and Year into my prompt and select the appropriate
records.

My prompt I believe is going to look for "mmddyyyy".

Any help would be appreciated.

Greg
 
R

Rick Brandt

Greg said:
I am trying to do a select query on one table.
This table has many records with a DATE type field in each record.
I would like to select all records from the table with a given month
and year, the day is not important in this query.
I would like to make this a "parameter" driven query. My intention is
to be able to enter Month and Year into my prompt and select the
appropriate records.

My prompt I believe is going to look for "mmddyyyy".

Any help would be appreciated.
Click to expand...

If YourDateField has no time component (all at midnight)...

WHERE YourDateField BETWEEN DateSerial([Please Enter Year], [Please Enter Month
Number], 1) AND DateSerial([Please Enter Year], [Please Enter Month Number] + 1,
0)

If YourDateField has a time component then make the final zero a one. If there
is a chance that some records do have a time of exactly midnight then I would
add 23 hours, 59 minutes and 59 seconds (or 82859 seconds) to the second
value...

WHERE YourDateField BETWEEN DateSerial([Please Enter Year], [Please Enter Month
Number], 1) AND DateAdd("s", 82859, DateSerial([Please Enter Year], [Please
Enter Month Number] + 1, 0))

Don't be concerned about the two parameter markers beign entered twice. As long
as you make each pair identical the user will only be prompted once for each.
 
O

OfficeDev18 via AccessMonster.com

Greg said:
I am trying to do a select query on one table.
This table has many records with a DATE type field in each record.
I would like to select all records from the table with a given month and
year, the day is not important in this query.
I would like to make this a "parameter" driven query. My intention is to be
able to enter Month and Year into my prompt and select the appropriate
records.

My prompt I believe is going to look for "mmddyyyy".

Any help would be appreciated.

Greg
Click to expand...
Since day is not important, I wouldn't include it in my lookup format.
Suggestion: Have your entered paramters, as shown in SQL, look something like
this: WHERE Month(DateField) = [Enter Month Number] And Year(DateField) =
[Enter year].

Yes, it means two entries, but that's easier than putting confusing data (the
day) in an entered parameter.
 
G

Greg

Sam and Rick,

Thank you both for your response! Your suggestions are similar and worked
out very well for me. My problem is resolved.
Thank you, again

Greg
 
Top