One parameter query for multiple fields?

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

Background:
I need to create a report and am using a parameter query to pull the data.

Problem:
I need all trainees who were in training in a particular month (say Feb). I
have 4 date fields that correspond to training classes or events. How do I
create a parameter query asking the "month & year" and apply this answer to
each of the 4 date fields? I dont want my users to have to complete four
different parameters with the same answer ever time they want to create a
report.

Thanks-Ryan





Backpacker
 
In query design, enter something like this in the Criteria row under the
first date field:
= [StartDate] And < DateAdd("m", 1, [StartDate])
Click to expand...

Below the Criteria row you will see several 'Or' rows.
On the first Or row under the next date field, enter the same expression.
On the next Or row under the next date field, enter it again.

To ensure Access understands the parameter correctly, declare it.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row using exactly the same name, like this:
[StartDate] Date/Time

That should get you out of this spot, but the real problem is that you have
repeating fields. It would be better to create a related table where one
trainee can be assigned many dates, rather than have many date fields in
this table.
 
What would the expression be to have them enter simply a "2" for Feb or "3"
for March and then "2009" for year? Since I want all records within an entire
month. I also want to reduce the risk of missing records since some months
have more days than others and the parameter requires the user to enter
between dates.


Thanks.


Allen Browne said:
In query design, enter something like this in the Criteria row under the
first date field:
= [StartDate] And < DateAdd("m", 1, [StartDate])
Click to expand...

Below the Criteria row you will see several 'Or' rows.
On the first Or row under the next date field, enter the same expression.
On the next Or row under the next date field, enter it again.

To ensure Access understands the parameter correctly, declare it.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row using exactly the same name, like this:
[StartDate] Date/Time

That should get you out of this spot, but the real problem is that you have
repeating fields. It would be better to create a related table where one
trainee can be assigned many dates, rather than have many date fields in
this table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ryan said:
Background:
I need to create a report and am using a parameter query to pull the data.

Problem:
I need all trainees who were in training in a particular month (say Feb).
I
have 4 date fields that correspond to training classes or events. How do I
create a parameter query asking the "month & year" and apply this answer
to
each of the 4 date fields? I dont want my users to have to complete four
different parameters with the same answer ever time they want to create a
report.
Click to expand...
Click to expand...
 
What would the expression be to have them enter simply a "2" for Feb or "3"
for March and then "2009" for year? Since I want all records within an entire
month. I also want to reduce the risk of missing records since some months
have more days than others and the parameter requires the user to enter
between dates.
Click to expand...

One possible approach would be to prompt for a date during the month (any date
during the month) and use a criterion
= DateSerial(Year([Enter a date:]), Month([Enter a date:]), 1) AND < DateSerial(Year([Enter a date:], Month([Enter a date:] + 1, 1)
Click to expand...

If the user enters 2/25 in response to the prompt, the criterion will find all
records from #2/1/2009# up to the instant before midnight at the start of
#3/1/2009#.
 
Back
Top