Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

find a month in a query i.e. find 'June'

Discussion in 'Information Technology' started by smason, Nov 4, 2005.

  1. smason

    smason
    Expand Collapse
    Guest

    hello

    i want to create a query which throws up a criteria request when run like
    Between[enter date] and [enter date] but i want to look for months as i
    have formated the date to june july august etc so i just want to type the
    month in the request

    can you help
     
  2. Loading...

    Similar Threads Forum Date
    Pathfinder Joins Sikh Philosophy Network! New SPN'ers Sep 5, 2016
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016
    Movies Ashdoc's movie review---Finding Fanny ( English version ) Theatre, Movies & Cinema Sep 15, 2014
    Leisure Can someone pls help me find this Waheguru Simran...?? Business, Lifestyle & Leisure Aug 5, 2014

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    The good news is you can create a parameterized query that, as you say,
    "throws up a criterion request".

    The bad news is that if you've save a date/time value in your table, EVEN IF
    you've formatted it to display as months, the value is a date/time. The
    more bad news is that April 1, 2000 and April 24, 2005 both have a month =
    April. Are you absolutely sure you want to have the user enter a "month"?!

    And the good news is you can use the Month() function against your table's
    date/time field to return the month "number" (e.g., January = 1, February =
    2, ... December = 12). In a query, include a new field something like:

    NewField: Month([YourDateField])

    and in the criterion "cell", use something like:

    Between [Enter # of first month - e.g., "2" for February] And [Enter #
    of last month]

    --
    Regards

    Jeff Boyce
    <Office/Access MVP>

    "smason" <smason@discussions.microsoft.com> wrote in message
    news:A252F8DA-07DD-427A-A3A7-A0F349217C57@microsoft.com...
    > hello
    >
    > i want to create a query which throws up a criteria request when run like
    > Between[enter date] and [enter date] but i want to look for months as i
    > have formated the date to june july august etc so i just want to type the
    > month in the request
    >
    > can you help
    >
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    How did you format the dates? Did you use the Format function in the query
    itself, or did you simply set the Format property of the query field?

    Using the Format property only changes how the value is displayed, not what
    the underlying value actually is.

    If you've used the Format function in the query:

    SELECT Field1, Field2, FORMAT(DateField, "mmmm") AS WhatMonth
    FROM MyTable
    WHERE WhatMonth = [Enter Month]

    then you will be able to use June as a criteria.

    Otherwise, while the dates may appear as June, July, ... in the results, you
    have to continue using the Between [Enter start date] and [enter end date]
    approach. (There are methods that you can use to calculate the start and end
    dates from a single input, but the first approach above is far simpler)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "smason" <smason@discussions.microsoft.com> wrote in message
    news:A252F8DA-07DD-427A-A3A7-A0F349217C57@microsoft.com...
    > hello
    >
    > i want to create a query which throws up a criteria request when run like
    > Between[enter date] and [enter date] but i want to look for months as i
    > have formated the date to june july august etc so i just want to type the
    > month in the request
    >
    > can you help
    >
     

Share This Page