Using the same parameter for two different columns in a query

[Alex]

Is it possible to use the same parameter for two different columns in a query?
I have a query that asks the user for the First Day Of The Month, and the Last
Day Of The Month, where the First Day Of The Month criteria is used in one
column, and the Last Day of The Month is used in another column. This seems
rather redundant given that both parameters refer to the same month/year. I'd
like the query to ask for the month and year, calculate the first day and last
day of the month from that, and use these calculated values as the criteria for
the query. I realize that I could accomplish this if I used a form to get to
the data, but I'd much rather do it all from within the query. Is this
possible?

 

[Guest]

This should work:

select DateSerial ([Enter the Year], [Enter the Month], 1),
DateSerial(([Enter the Year], [Enter the Month] + 1, 0);

You have two entries to make, but this will give you the first and last day
of the month in the year that you ask for. This could be used for a BETWEEN
clause for example.

Good Luck!

 

[Guest]

Additional info: the SQL shown will display in the Query grid as:

Expr1: DateSerial ([Enter the Year], [Enter the Month], 1)
Expr2: DateSerial ([Enter the Year], [Enter the Month] + 1, 0)

So it will reuse the prompt values.

Good Luck!

 

[Marshall Barton]

Is it possible to use the same parameter for two different columns in a query?
I have a query that asks the user for the First Day Of The Month, and the Last
Day Of The Month, where the First Day Of The Month criteria is used in one
column, and the Last Day of The Month is used in another column. This seems
rather redundant given that both parameters refer to the same month/year. I'd
like the query to ask for the month and year, calculate the first day and last
day of the month from that, and use these calculated values as the criteria for
the query. I realize that I could accomplish this if I used a form to get to
the data, but I'd much rather do it all from within the query. Is this
possible?

If you do not specify the day of the month, Access assumes
the first. E.g Jun 2005, 6/2005 and Jun1 1 2005 are all
the same date.

You can calculate the end of the month:
DateSerial(Year([Enter Date]), Month([Enter Date]) + 1, 0)

If you don't trust your users to enter a date that starts on
the first on the month, you can force it to the first:
DateSerial(Year([Enter Date]), Month([Enter Date]), 1)

 

[Alex]

Thank you all. That's exactly what I was looking for.