Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    72

    Unanswered: Using dates in SQL

    The following SQL seems to be ignoring the >= date and returns records that are <= date.

    Any ideas why?

    SELECT CALL_HISTORY.CLIENT_ID, CALL_HISTORY.DATE_LOG, CALL_HISTORY.USER, CALL_HISTORY.COMMENTS, USERS.USERS_IDENTITY, CLIENT.COMPANY_NAME
    FROM USERS, CLIENT INNER JOIN CALL_HISTORY ON CLIENT.CLIENT_ID = CALL_HISTORY.CLIENT_ID
    WHERE (((USERS.USERS_ID)=[CALL_HISTORY].[USER]) AND (([FORMS]![CALL_HISTORY_REPORT]![CONSULT])=[USERS].[USERS_IDENTITY] Or ([FORMS]![CALL_HISTORY_REPORT]![CONSULT])="") AND ((Format([CALL_HISTORY].[DATE_LOG],'dd/mm/yy')) >= Format([FORMS]![CALL_HISTORY_REPORT]![CONSULTANT_DATE_FROM],'dd/mm/yy')))

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i betcha it's your parentheses and mix of AND and OR

    you have:

    Code:
    WHERE (
            ( (X)=Y ) 
        AND ( (Z)=Q
           OR (Z)=""
            ) 
        AND ( (Format(D,'dd/mm/yy')) >= Format(H,'dd/mm/yy')
            )
          )
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jun 2002
    Posts
    72
    Don't think so, I still get the same result with the following:

    SELECT [CALL_HISTORY].[CLIENT_ID], [CALL_HISTORY].[DATE_LOG], [CALL_HISTORY].[USER], [CALL_HISTORY].[COMMENTS], [USERS].[USERS_IDENTITY], [CLIENT].[COMPANY_NAME]
    FROM USERS, CLIENT INNER JOIN CALL_HISTORY ON [CLIENT].[CLIENT_ID]=[CALL_HISTORY].[CLIENT_ID]
    WHERE [USERS].[USERS_ID]=[CALL_HISTORY].[USER] And [FORMS]![CALL_HISTORY_REPORT]![CONSULT]=[USERS].[USERS_IDENTITY] And Format([CALL_HISTORY].[DATE_LOG],'dd/mm/yy')>=Format([FORMS]![CALL_HISTORY_REPORT]![CONSULTANT_DATE_FROM],'dd/mm/yy');

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DOH!! (smacks self on forehead)

    it was staring at me all along, even more clearly in my own reply!!

    you are comparing FORMAT strings as strings, not dates

    so 13/11/2002 will come after 02/12/2002

    you'll want to compare dates in string format in major-to-minor sequence, i.e. yyyy/mm/dd



  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Look at this


Posting Permissions

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