Parameter Query Help nedeed

[dfuas]

Hi

I have the following Access SQL query:

SELECT Trade.Ref_No, Trade.Carbon_Project, Trade.HostName, Trade.Country, Trade.Retainer_fee_to_Receive, Trade.Fees_Payable, Trade.Due_Date

FROM Trade

WHERE ((Month([Due_Date])=[?] And Month([Due_Date])=[?]));


I have put the information from WHERE into the Criteria of Due_Date in the Query Design View.
What I am trying to achieve is to be able to view the records based on two months parameters. i.e say to view the records between Jan 06 to Dec. 06.
Somehow I only get asked the first month input parameter from above query.

Can anyone pls help me on how I should get this?

Thanks

dfuas

 

[pbaldy]

You would have to use different text between the brackets to be asked twice. Since you used the same thing (?), Access assumes you want to use the same criteria.

Having said that, this is more complicated than you realize. Even if it works the way you have it set up, you will get those records whose month = January OR December. Nothing in between. To do that, you'd need:

WHERE Month([Due_Date]) Between [Start Month] And [End Month]

However, that code makes no allowance for year, so you'll get all years. Either have the user enter the full date values and drop the Month() from your criteria, or add a year criteria.

 

[pbaldy]

I need to correct myself. The way you have it set up, you wouldn't get any records, since no record can have a month of 1 AND 12. That's what the AND in your WHERE clause will do. If you had an OR, you'd get both months. In any case, the Between statement is what you want.

 

[dfuas]

How would I add a year say 2006? Sorry this is first time i am trying this sort of query. thanks

 

[pbaldy]

Personally I'd go with having the user enter the full dates, but if you want to go month/year, try:


WHERE Month([Due_Date]) Between [Start Month] And [End Month] AND Year([Due_Date]) = [Enter Year]

 

[dfuas]

thanks, that is working just as I want it.

 

[raskew]

Hi -

pBaldy has answered your request, but the need to span one or more years at some later date is a real possibility. Here's how you might handle it, using mm/yyyy as your parameters. (Orders3 is a copy of Northwind's Orders table):

SELECT Orders3.OrderID, Orders3.OrderDate
FROM Orders3
WHERE (((Orders3.OrderDate) Between DateValue([start mm/yyyy]) And DateAdd("m",1,DateValue([end mm/yyyy]))-1));

If you responded to [start mm/yyyy] with 12/1994 and [end mm/yyyy] with 02/1995, the query returns all records between 12/1/1994 and 2/28/1995.

Bob

 

[dfuas]

that is really great. I can't thank you enough.

dfuas