Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Text field >= <= pulling wrong values from an Access database

    Hello,

    I've got an access database I'm pulling records where the date is >= fromDate and where date is <= toDate. The date field is actually a text field in the database; don't ask me why anyone would put dates in a text field I didn't write the database i just wrote the code to run some reports on the data my boss actually wrote the database and there are so many scripts that use this database it wouldn't be feasible to change the field to a date field.

    Anyway on to the problem here's my sql statement:

    Code:
            strSQL = "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
            strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
            strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
            strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
            strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM emptime_all WHERE crew = '" & crew & "' "
            strSQL += ")"
            strSQL += "WHERE Tdate <= '" & Request.Form("toDate") & "'"
            strSQL += ")"
            strSQL += "WHERE Tdate >= '" & Request.Form("fromDate") & "'"
            strSQL += ")"
            strSQL += " WHERE job_num = '" & Request.Form("jobNum") & "'"
            strSQL += ")"
            strSQL += " ORDER BY emp_NUM, Tdate;"
            rs.open(strSQL, adoCon)
    the date format is mm/dd/yyyy

    and the problem it seems to be ignoring the year so if i try to pull dates between 10/20/2008 and 10/24/2008 i get all these dates

    10/20/2004
    10/20/2008
    10/21/2004
    10/21/2008
    ect....

    Even though it's text it should still be comparing ascii values and outputting the correct dates. Any help would be greatly appreciated. We really need to get this fixed before the new year rolls over there isn't supposed to be anything in there except 2008 right now but we will be having some serious problems if we don't get this fixed by 2009.

  2. #2
    Join Date
    Oct 2008
    Posts
    4
    Leaving work heading to new orleans for the weekend I'll check back Tuesday. I hope someone can point me in the right direction and thanks in advance again for any help.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Use # instead of ' for the criteria against the date fields.

    strSQL = "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM emptime_all WHERE crew = '" & crew & "' "
    strSQL += ")"
    strSQL += "WHERE Tdate <= #" & Request.Form("toDate") & "#"
    strSQL += ")"
    strSQL += "WHERE Tdate >= #" & Request.Form("fromDate") & "#"
    strSQL += ")"
    strSQL += " WHERE job_num = '" & Request.Form("jobNum") & "'"
    strSQL += ")"
    strSQL += " ORDER BY emp_NUM, Tdate;"
    rs.open(strSQL, adoCon)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access isnt ignoring anything, its doing precisely what you requested.
    the problem is the person who designed this db took a short cut (storing a date field as a string. thats the issue)

    you have already identified the solution.. change the datatype. the fact there are so many scripts (whatever they are in this context... do you mean queries?, forms?, vba?) is in my book irrelevant. you are adding on a kludgey fix to a problem... next time you have to make changes you've got to pick up the problem again. as time goes on people will think its your problem not the original authors problem that such crappy code has slipped through into the wild.

    what you 'could' try doing is expressly coercing the string into a date
    eg select my,column,list, cdate(tdate) as mydatevalue from my table
    where mydatevalue etc....
    order by some,column,or,other,mydatevalue

    what you 'should' do is go back to the db,
    insert a new column, call it say, mydatevalue
    run a query which inserts the values from tdate into mydatevalue
    update mytable set mydatevalue = cdate(tdate)

    rip out any reference to tdate replacing it with mydatevalue
    make sure any text boxes where the user can change the date value has a format mask that limits data entry to a valid date, consider using a calendar control

    BTW your SQL statement looks very odd to me what (in Ebglish) are you trying to retrieve from the db

    when retrieving records from a date range consider using
    where mydatevalue between fromdate and todate

    bear in mind that access/jet store dates as a date time value so if you want to do comparatives you may need to add one to the date
    access/jet stores 15/10/2008 as 15th Oct 2008 @ 00:00:00
    so if you want records that were on the 15th to be included you have to add a day IF you want to use the < construct otherwise any reocrds that occurred after 00:00:00 will be rejected
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by pkstormy
    Use # instead of ' for the criteria against the date fields.

    strSQL = "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM("
    strSQL += "SELECT emp_NUM, lname, class, Tdate, hours, crew, job_num, pu, rig, pd, fname, tool, minitial FROM emptime_all WHERE crew = '" & crew & "' "
    strSQL += ")"
    strSQL += "WHERE Tdate <= #" & Request.Form("toDate") & "#"
    strSQL += ")"
    strSQL += "WHERE Tdate >= #" & Request.Form("fromDate") & "#"
    strSQL += ")"
    strSQL += " WHERE job_num = '" & Request.Form("jobNum") & "'"
    strSQL += ")"
    strSQL += " ORDER BY emp_NUM, Tdate;"
    rs.open(strSQL, adoCon)
    Oops - my bad - Yes - Disregard the above sql unless like healdem mentioned, the field type is a date/time field type and the other points he made are resolved. I will always have an actual date/time field type in the table along with any text fields representing dates. I see date values in text fields every now and a lot of the ones I see usually stem from data exports where the export process didn't allow for date/time formatting (or the db didn't allow date/time fieldtypes - ie. some pd data) or it was just exported incorrectly (or inexperienced developers). About 4 years ago I hired an IT Consultant to do some programming work. He was fresh out of college and would like to define his date fields as a text type. I would not accept the program he was designing (and pay him) until he reformatted the field types to date/time (he said he was taught that in college.) Regardless - I told him we wanted to do time range queries and his solution was to keep changing the query criteria and that was unacceptable to me.
    Last edited by pkstormy; 11-02-08 at 19:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2008
    Posts
    4
    Thanks for the help guys. Yeah it was my boss that wrote the db and everything he knows about programming is self taught, so i guess it never occurred to him that it was a bad idea to make a date field text. He actually makes all his fields text. I just call him a noob and ignore it most the time.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gainer
    He actually makes all his fields text. I just call him a noob and ignore it most the time.
    Well done you - that is a dreadful, but all too common, error I'm afraid.

    If you are interested and want to learn more have a look at texts on relational design and pay particular attention to domain constraints (appropriate data type selection is the most basic element of domain constraints).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Oct 2008
    Posts
    4
    Finally got my code working correctly. I ended making a new column and running a query to copy all the dates from the text field to the new date field I created. It took all day to run the query 230,000 rows it had a few bad dates in there I had to fix that would throw an error every time it hit one so I would have to restart the query. Also ended up writing a stored procedure to update the date field every time someone edits a date or makes a new entry. This will also help keep bad dates out of the data base. My boss's scripts have no error checking so people would screw it up all the time by entering something like 10/20/22008 which would make my code throw errors when it was trying to pull dates. Now to move everything from the devserver to the webserver and edit my boss's code to use my stored procedure eh....

    Thanks for the help guys.

Posting Permissions

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