Between Clause (Dates stored as Text)

• rehook2003

  SSC-Addicted

  Points: 499

  More actions

  January 14, 2004 at 1:21 am

  #160595

  Hello,

  I'm having some trouble with a small app that I have been given to work on.

  I'm trying to retrieve records based on a date range.

  The dates are stored in the Access database as text

  When I try to retrieve the records (ex. Between 01/01/2003 and 12/31/2003)

  I still get all of the records dated in the 2004 year.

  Here is the statement below

   rst.Open "SELECT * FROM tblDeposits WHERE " _

                  & "uDate BETWEEN '" & StartDate & "' AND '" & EndDate & "' " _

                  & "AND MemberID = '" & MemberID & "' ", cnn

   

  I cant change the field to date/time because it has already been distributed

  Any Suggestions?

  Thank you

  Rick

   

   

• dchilton

  SSC Veteran

  Points: 293

  More actions

  January 14, 2004 at 8:27 am

  #489984

  Try using the CDate («date»)  function.

   rst.Open "SELECT * FROM tblDeposits WHERE " _

                  & "CDate (uDate) BETWEEN '" & StartDate & "' AND '" & EndDate & "' " _

                  & "AND MemberID = '" & MemberID & "' ", cnn

  You may also need the IsDate («varexpr»)  function if any of the existing data is invalid.

   rst.Open "SELECT * FROM tblDeposits WHERE " _

                  & "CDate (uDate) BETWEEN '" & StartDate & "' AND '" & EndDate & "' " _

                  & "AND IsDate (uDate) = TRUE AND MemberID = '" & MemberID & "' ", cnn