Results 1 to 4 of 4

Thread: month (2003)

  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    month (2003)

    I have a query that contains a date/time field that when run, I want to enter a month and year and have the data returned only for that month and year. I would like the use to be able to enter Jan04 or Jan 04.

    thanks,
    Jackal

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: month (2003)

    You will need to do some date calculations to achieve that - and you will have to subvert some of the inherent Access capability. I would be inclined to put the criteria on a form and use combo boxes to force only valid months and years to be chosen. Then you can do the date calculations and set the criteria for the query in VBA.
    Wendell

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

    Re: month (2003)

    For this type of thing, I usually do what Wendell suggests, use comboboxes on form to list valid date ranges for month and year (in some cases, if not all months will have data, the Month combo is "filtered" by the value in Year combo). But if you want to do this using query parameters, you could use something like this example (uses Northwind "Orders" table):

    PARAMETERS [Enter Mon/Year (mmm-yy):] Text ( 255 );
    SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate
    FROM Orders
    WHERE (((Month([OrderDate]))=IIf(IsDate("01/" & Left$([Enter Mon/Year (mmm-yy):],3) & "/2000"),Month("01/" & Left$([Enter Mon/Year (mmm-yy):],3) & "/2000"),0)) AND ((Year([OrderDate]))=IIf(IsDate("01/01/" & Right$([Enter Mon/Year (mmm-yy):],2)),Year("01/01/" & Right$([Enter Mon/Year (mmm-yy):],2)),0)))
    ORDER BY Orders.OrderDate, Orders.ShippedDate;

    In this example the month and year are entered as a text string which is parsed by the two expressions used as WHERE criteria to create constructive dummy dates that are evaluated by Month and Year functions. Note that the two expressions use the same parameter so user only has to enter once. As long as first 3 characters equate to a month and the final 2 characters equate to a valid year the query should work as intended. The following parameter values produced valid results:

    jan 98
    feb-97
    APR98
    JAN1997
    Dec 97
    OCT::1996

    These parameter values resulted in an empty result set:

    abc 00
    xyz 3
    ?

    The convoluted IIf statements and IsDate function are used to avoid dreaded "The expression is too complex to be evaluated" and "Object is no longer valid" error messages. Note I am using a standard US-English installation of Access; it is possible the SQL would have to be modified in some other locales.

    HTH

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: month (2003)

    Nice job Mark, I was looking for this and was afraid to ask.

Posting Permissions

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