Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Value in a date? (2003)

    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.

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Value in a date? (2003)

    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>
    Thanks,

    pmatz

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parameter Value in a date? (2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Value in a date? (2003)

    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.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Value in a date? (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •