Results 1 to 4 of 4

Thread: Date Selection

  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Selection

    I need some help in figuring out the most efficient way to select dates by mm/yyyy. My users enter mm/dd/yy, but need to report by mm/yy. I've done all sorts of convoluted strings and dateparts, but it's very awkward. Any ideas? (this is my first communication, so I welcome protocal feedback!)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Selection

    Hi,
    What exactly are you trying to do? If for example you want to group records in a query by mm/yyyy then I'd suggest simply adding a new field to your query along the lines of
    myDate:Format([datefield],"mm/yyyy") and grouping on that. If not, can you post something more specific?
    Thanks,
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Selection

    Here is more detail for my date selection question. EG: In the "Effective Date" field, a user enters the following dates: 01/02/02, 01/09/02, 01/19/02, 02/18/02, 02/03/02...etc. The user wants the report by month and year. How can I get all dates within the month by requesting just 01/2002? This application has about 10 tables, each with 3 or 4 date fields that need this! Thanks for help!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Selection

    Hi,
    Basically you would do what I described previously i.e. add a field to your query such as
    EffDate: Format([Effective Date],"mm/yyyy")
    and also add a text-type parameter (NOT date-type) to your query - e.g. [Reporting Date]. You then put [Reporting Date] in the criteria for the EffDate column of your query. When the query runs it will prompt for a reporting date, the user can enter 01/2002, and the query will only return the relevant records.
    I hope that's clear - if not let me know.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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