Using Month and Year only in Parameters!

[Brockers]

I'm trying to pull out all the Memberships in a Report that expire in a particular Month i.e. July 2003 (UK Format 07/2003)

I can pull the appropriate records out using the Query Criteria: Like *07/2003

Can't seem to get it to work with a Parameter Prompt instead. The field it's using is a calculation using the DateAdd Function to add the Membership Duration (12 Months)to the Membership Start Date!

Any ideas anyone, I really feel I should know this but it's foxed me!
Regards

Brockers
Instructor
New Horizons Manchester England

 

[BlackKnight]

Hi,

I am not sure if this is what you are asking for but it is a query that prompts for 2 parameters: month (1-12) and a year. Modify as needbe.

SELECT Month(DateAdd("m",12,[StartDate])) AS ExpMonth, Year(DateAdd("m",12,[StartDate])) AS ExpYear
FROM tblMember
WHERE (((Month(DateAdd("m",12,[StartDate])))=[Mth]) AND ((Year(DateAdd("m",12,[StartDate])))=[Year]));

Have a good one!
BK

 

[raskew]

For an example, copy/paste this to a new query in Northwind.
Use 08/94 and 11/94 when prompted for start and end dates.
<code>
PARAMETERS [enter start mm/yy] Text, [enter end mm/yy] Text;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between DateValue([enter start mm/yy]) And DateSerial(Year(DateValue([enter end mm/yy])),Month(DateValue([enter end mm/yy]))+1,0)));
</code>

If you want to check just one month's records, you could use this:
<code>
PARAMETERS [enter start mm/yy] Text;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between DateValue([enter start mm/yy]) And DateSerial(Year(DateValue([enter start mm/yy])),Month(DateValue([enter start mm/yy]))+1,0)));
</code>