Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: SQL Date Range Issues

    I have an SQL query and I am trying to introduce a date range selection, i.e. to be able to select those records only with dates between X and Y (or >= X and <=Y)..

    I have the following:

    HTML Code:
    "SELECT Client.ClientID, Client.Title, Client.LastName, Client.DateOfBirth, Client.FirstName, Client.MaritalStatus, Client.City, Client.Country " & _
             "FROM Client " & _
             "WHERE (((([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null)=True) " & _
             "AND (([City]=[Forms]![Main Form]![cboCity] Or [Forms]![Main Form]![cboCity] Is Null)=True) AND (([Country]=[Forms]![Main Form]![cboCountry] Or [Forms]![Main Form]![cboCountry] Is Null)=True) " & _
             "AND ([DateOfBirth] >= " & Format([Forms]![Main Form]![cboFrom], "\#dd-mm-yyyy\#") & " AND ([DateOfBirth] <= " & Format([Forms]![Main Form]![cboTo], "\#dd-mm-yyyy\#") & ")))"
    It does work in principal, HOWEVER... >= doesn't work... and by far the biggest issue if the dates are not filled in, I tried all sorts of ISNULL or IS NULL = true but no luck so far.. database attached!

    P.S You need to click RESET button on the main form to get all the records.

    Much obliged!
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Two things concerning Date/Time data type in Access:

    1. In Access SQL the Date format is always "mm/dd/yyyy" (month/day/year), whatever the International Settings of Windows can be. Moreover, dates must be enclosed by "#" (pound or sharp character) : #12/25/2009#.

    2. Date/Time is the operative word: the data are stored with a Date part and a Time part. If you store something like this: SET MyDate = Now(), you cannot expect to successfully use comparisons such as: WHERE MyDate = "#12/25/2009#, because Access will find a mach if the time part is zero (midnight) only. So if you don't care for the time part, store the data like SET MyDate = Date. This way, the Time part will always be equal to zero and the comparison will be successfully possible. If the data are already stored in Date/Time format with a significant (non-zero) Time part, you must use a conversion function in your comparison (e.g. WHERE Format(MyDate, "mm/dd/yyyy") = '12/25/2009') or use other comparison operators or functions (BETWEEN, DateDiff, etc...).
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    When dealing with SQL in MSaccess ALWAYS query the DATES AS mm/dd/yyy even tho you see then in the database as dd/mm/yyy

    Code:
    Function USADATE(Adate)
    USADATE = format(Adate,"MM/DD/YYYY")
    End Function
    I have the above function in a module and I always call it when make my SQL
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Mar 2010
    Posts
    6
    Thanks for replies but as I am quite new to this I am a bit struggling to implement what you are saying as it still doesn't seem to work..

    I don't want to use a function, as there is only one place in code I use dates, so I think there i snot harm formatting within the SQL string. I think I followed your advise in chaning formatting to what is below, but this just returns nothing.. so I always get nothing back.. it also doesn't resolve the matter of when dropdown box is not selected, i.e. ISNULL as it gives a syntax error.. Sorry to be a pain, but I have never dealt with Access before and this db for my work is clearly taking the better of me. Thanks a lot

    Mike

    HTML Code:
    SQLStr = "SELECT Client.ClientID, Client.Title, Client.LastName, Client.DateOfBirth, Client.FirstName, Client.MaritalStatus, Client.City, Client.Country " & _
             "FROM Client " & _
             "WHERE (((([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null)=True) " & _
             "AND (([City]=[Forms]![Main Form]![cboCity] Or [Forms]![Main Form]![cboCity] Is Null)=True) AND (([Country]=[Forms]![Main Form]![cboCountry] Or [Forms]![Main Form]![cboCountry] Is Null)=True) " & _
             "AND ([DateOfBirth] >= " & Format([Forms]![Main Form]![cboFrom], "mm/dd/yyyy") & " AND ([DateOfBirth] <= " & Format([Forms]![Main Form]![cboTo], "mm/dd/yyyy") & ")))"
    Last edited by itipu; 03-22-10 at 04:23.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You are still not enclosing your date values in # symbols:
    Code:
    "AND ([DateOfBirth] >= #" & Format([Forms]![Main Form]![cboFrom], "mm/dd/yyyy") & "# AND ([DateOfBirth] <= #" & Format([Forms]![Main Form]![cboTo], "mm/dd/yyyy") & "#)))"
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    also check the date you are comparing is only a date as stated earlier Access/JET stores dates as date/time values. so depending on how you stored the date it may be include a time element

    when you compare date/times you have to allow for the time element or never use time element

    so to find all values between a certain date say 15th march and 21st april
    you need to specify where mydatecolumn >=#03/15/2009# and mydatecolumn < #04/22/2009#
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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