Dates

  • Thread starter Thread starter Greg
  • Start date Start date
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
 
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.
 
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.
 
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
 
Back
Top