Results 1 to 10 of 10

Thread: Querying Dates

  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Querying Dates

    I need to query dates such as,

    The month of march of 2004

    I have the dates set up as 03/15/04.

    I can't seem to get it to show me results for March of 2004, for example.

    What do I need in the criteria box?

    Thank you!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    between #3/1/04# and #3/31/04#

  3. #3
    Join Date
    May 2004
    Posts
    2
    I knew it had to be simple. Thank you, I was missing a lb. sign.

    *sigh*

    thanks!

  4. #4
    Join Date
    Mar 2004
    Location
    THAILAND
    Posts
    15

    It may help you

    Try this one.

    cc : "The Month of "+MonthName(DatePart('m',Now()))+" of "+CStr(DatePart('yyyy',Now()))
    1

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use between #2004-03-01# and #2004-03-31 23:59:59#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    What I do is to create a query and add two more fields. Let's say your date fields is called myDate. Add two fields in your query

    myDateMonth: Year([myDate])

    myDateYear: Year([myDate])

    Use parameters as criteria for the two added fields in your query so that only records. The SQL should look something like this

    SELECT Table1.myDate, Month([myDate]) AS [myDateMonth], Year([myDate]) AS [myDateYear]
    FROM Table1
    WHERE (((Month([myDate]))=[MonthWanted]) AND ((Year([myDate]))=[YearWanted]));
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cosmos75, that's fine, it works, but unfortunately will not utilize an index on the myDate field

    this may not matter in small databases, but in large databases, you want to write your queries in a way that allows indexes to be used
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    r937,

    What about using the WHERE condition this way:

    SELECT Table1.myDate,
    FROM Table1
    WHERE (((Table1.myDate) Between DateSerial([YearWanted],[MonthWanted],1) And DateSerial([YearWanted],[MonthWanted]+1,1)-1));

    By utilizing index, you mean to say that the date field here is indexed?
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's it, apply the computations to literals or DATE(), not to the date column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Thumbs up Thanks!

    r937,
    Cool!
    THANKS!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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