Parameter Value in a date? (2003)

[OverPower]

Greetings.

Could someone please advise whether it's possible to use a parameter in order to get a query to return specific results from a date field?

I.E. If we have Invoice Dates for 01/03/04, 02/03/04, and 01/04/04, we just want to see what invoices were sent out in March. Therefore, to get the query to return just Invoices for March, we would use criteria such as:
*/[Enter the Month]/04

From the Access Help File, it appears that these need to be joined with ampersands, thusly:
LIKE "*" & [Enter any character to search by: ] & "*"

I've tried to enter my Query in this format (IE: LIKE "*" & [Enter the month] & "04"), but it doesn't work.

I'm guessing it's not possible to enter a Parameter in a Date Field.

P.S. i tried using # symbols, but that didn't work either.

Thanks for any help you can provide.

[Matz]

HI!

One method is to use the 'DatePart' function provided by Access. An example for you would be to have the following in your criteria for the field in question:

DatePart("m",[name of your field])=[Enter month as a number]

when the query is run, it will ask you for the parameter 'Enter month as a number'. So say use 3 for March. Then the query will get all the values where the month is = to 3 in the field name that you have entered.

For more help on DatePart look at the Access help.

Hope this helps / makes sense <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[MarkD]

I'd use the VBA Month (and Year, if necessary) functions to define parameters for date. Example (Northwind Orders table):

PARAMETERS [Enter Month (1 to 12)] Short, [Enter Year (4 digits)] Short;
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Month([OrderDate]) AS OrderMonth, Year([OrderDate]) AS OrderYear
FROM Orders
WHERE (((Month([OrderDate]))=[Enter Month (1 to 12)]) AND ((Year([OrderDate]))=[Enter Year (4 digits)]))
ORDER BY Orders.OrderDate;

This would be more reliable than using "Like" operator with text string, which can depend on user's Windows Regional Date settings to work correctly. (Note "Short" in Parameters clause signifies Integer data type - an Integer in Access being only 16 bits in length.)

HTH

[HansV]

I would add calculated columns to the query:
Month([InvoiceDate])
and
Year([InvoiceDate])

where InvoiceDate is the name of the field. Clear the Show check boxes for these columns, and set the criteria to [Enter Month] for the month column and 2004 for the year column.

If you want to use the date field itself, you could use
Like "*/" & [Enter Month] & "/2004"
assuming dd/mm/yyyy format, but this will fail if somebody uses US mm/dd/yy format.

[OverPower]

Thanks to everyone for their replies! That's great. As always, I guess there's more than one way to . . . well, you know the rest.

P.S. FWIW, mine didn't work initially because I had typed 04 instead of 2004!
<img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

Don't ya hate when that happens? Sorry to bother you, although I've learned a few tips, so as long as you don't mind, I guess it was worth it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Thanks again for ALL your help.