Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Date Range MO YEAR qry on TEXT STRING DATES

    This works wonders for Make Tables and counts. . .
    Code:
    SELECT TblSandicorDownload.StNo
    , TblSandicorDownload.StName
    , TblSandicorDownload.City
    , TblSandicorDownload.Dte
    , TblSandicorDownload.ListName
    FROM TblSandicorDownload
    WHERE (((Left([Dte],2))=[Enter Month]) AND ((Right([Dte],4))=[Enter year]));
    . . . but what if I need a date range?

    I tried entering 1,2,3 in the month parameter but that was not accepted and no records were produced.

    I also tried 1 Or 2 Or 3 in the month parameter which also failed.

    OK I'm trying this now
    Code:
    SELECT TblSandicorDownload.StNo
    , TblSandicorDownload.StName
    , TblSandicorDownload.City
    , TblSandicorDownload.Dte
    , TblSandicorDownload.AskPrice
    , TblSandicorDownload.SalePrice
    , TblSandicorDownload.ListID
    , TblSandicorDownload.ListName
    , TblSandicorDownload.SellID
    , TblSandicorDownload.SellName
    FROM TblSandicorDownload
    WHERE (((Left([Dte],2))>=[enter START Month]) AND (((Left([Dte],2))<=[enter END Month]
    AND ((Right([Dte],4))=[Enter year]))
    All I'm getting is a syntax error but no highlighting the error. Ops, I found the syntax error.

    A UNION ALL query works but I don't want 12 queries for each month!

    Any ideas . . .

    Thanks . . . Rick
    Last edited by Rick Schreiber; 05-14-12 at 23:48.

  2. #2
    Join Date
    Apr 2012
    Posts
    3

    Year, Month, Day Functions

    Is there any reason you're not using the Year, Month, and Day functions in the Where clause of your query? It'd be easier to match year, month, and day separately. Have your users put it in all separately, or start looking into 'query by form's for another solution.
    Last edited by shibal; 05-14-12 at 23:58.

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Follow this . . .

    The date fields is defined as text and Access will not allow queries on date text strings.

    http://www.dbforums.com/microsoft-ac...right-9-a.html

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Qry TEXT DATE TYPE Date Range

    This works flawlessly on a text type date as mm/dd/yyyy
    Code:
    SELECT TblSandicorDownload.StNo
    , TblSandicorDownload.StName
    , TblSandicorDownload.City
    , TblSandicorDownload.Dte
    , TblSandicorDownload.AskPrice
    , TblSandicorDownload.SalePrice
    , TblSandicorDownload.ListName
    FROM TblSandicorDownload
    WHERE (((Left([Dte],2))>=[enter START month]) And ((Left([Dte],2))<=[enter END month]) And ((Right([Dte],4))=[enter YEAR]));
    Hope it helps someone.

    And a BIG thanks to those in my earlier threads as I did learn much from them during the process.

    Rick

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Rick Schreiber View Post
    . . . but what if I need a date range?

    I tried entering 1,2,3 in the month parameter but that was not accepted and no records were produced.

    I also tried 1 Or 2 Or 3 in the month parameter which also failed.
    You can use the IN operator if you want to retrieve data that can have a value among several possibilities:
    Code:
    ... WHERE <SomeColumn> IN ( <Value1>, <Value2>, <Value3> );
    This can replace:
    Code:
    ... WHERE <SomeColumn> = <Value1> OR <SomeColumn> = <Value2> OR <SomeColumn> = <Value3> );
    Have a nice day!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    This works flawlessly on a text type date as mm/dd/yyyy
    Code:
    ...
    WHERE (((Left([Dte],2))>=[enter START month]) 
    And ((Left([Dte],2))<=[enter END month]) 
    And ((Right([Dte],4))=[enter YEAR]));
    i guess you don't want to hear that your dates should not be stored as text strings

    also, that WHERE clause is going to fail you if you want to go from november of one year to february of the next
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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