Results 1 to 10 of 10
  1. #1
    Markb384 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    44

    Changing Date Formats in SQL Query Not Working

    Hi guys and gals

    I am really struggling with the use of dates in an SQL query. I had a previous thread on here (https://www.accessforums.net/access/...ull-43532.html) and my solution is to create another temporary table with SQL/VBA and use Dmax on that instead.

    However, I think American formats are still screwing me over, despite my best efforts, as when sdate is something along the lines of 2/5/14 for instance, no entries are transferred to the temp table. EDIT: Brief caveat, I am based in the UK so I am trying to use dates in the format of ddmm rather than the American mmdd.



    My code is...
    Code:
    Dim strSQL As String'create a temp table to store all the baseline dates
        DoCmd.RunSQL "CREATE TABLE tempTbl ([ID] COUNTER CONSTRAINT ndxID PRIMARY KEY, [Survey Date] DATETIME);"
    
    'create SQL code to search for baseline dates and transfer them to the temp table
        strSQL = "INSERT INTO tempTbl SELECT Results_TO10.[Survey Date] FROM Results_TO10 WHERE Results_TO10.[Equipment ID] = '" & equipID _
                & "' AND Results_TO10.[Baseline?] = True AND Format(Results_TO10.[Survey Date],""ddmmyyyy"") < " & Format(sdate, "ddmmyyyy") & ";"
    
    'run the SQL line
        DoCmd.RunSQL strSQL
            
        basedate = DMax("[Survey Date]", "[tempTbl]")
    
    'delete temp table
        DoCmd.RunSQL "DROP TABLE tempTbl;"
    My current table is currently made up of 11/1, 18/2, 19/2, 21/2, 22/2 and 2/5, where the bold items are True for baseline. If the user chooses a bold date, other code is ran instead, which works fine. When the 18/2 is chosen, the code returns 11/1, which is perfect. When 21-22/2 are chosen, the 19/2 is returned, again perfect. However, when the 2/5 (or any other number which can be intepreted as mm/dd, such as 9/4), no records are transferred to the temp table. Further investigation, such as changing the 18/2 to the 8/2 has the same result, i.e. not transferring the data, therefore adding to my suspicions that it is a date format issue.

    I thought the use of format() would force the issue, but it doesn't appear to be working. Anyone got any ideas?

    Thank you

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I thought the use of format() would force the issue, but it doesn't appear to be working. Anyone got any ideas?
    I will say this much. Do NOT use the FORMAT function with Dates in your Where clause. Using the FORMAT function converts whatever you are formatting to TEXT. Hence, you may get unexpected results since you are then doing Text comparisons instead of Date comparisons.

    The FORMAT function is useful for displaying how a date looks on a Report, but is not something you typically want to use if you wish to use the date in any sort of calculations or comparisons.

  3. #3
    Markb384 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    44
    Hi JoeM

    Not using any sort of Format in the WHERE clause appears to not pull out any results into my temp table. My original field and the variable I am comparing it to are both in the format dd/mm/yyyy yet I read somewhere that Access still defaults to American when doing the comparison. What would the alternative method be?

    I'm not too concerned with the date becoming a text format, as Dmax still appears to recognise it and from then onwards I only use it for reference, and not for any calculations.

    Thank you

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The difference in American and UK date formats shouldn't really have any bearing on the results, as dates are not stored in Access that way. Dates are stored in Microsoft programs as a number, specifically the number of days since January 1, 1900.

    What the difference in formats can affect is data entry (due to Regional settings), i.e. you enter 1/6/2000, and it changes it to 6/1/2000. But once the data are already in your table, changing the "format" does not change the date value. The date value is already "locked in" at that point.

    So, I think important questions to ask are:
    1. Are [Survey Date] and sdate both set up as Data Type fields?
    2. How/where is sdate defined?
    3. Do the dates really have the values you think they do? You can convert them to their numeric equivalent like this to see if your date comparisons will work:
    Format([Survey Date],"0")

  5. #5
    Markb384 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    44
    Hi JoeM

    [Survey Date] and sdate are both originally chosen using a datepicker on a form and appear to be the same as far as I can tell. sdate is defined as a Date variable and is passes as a Date through to the function where it is used.

    Formatting the date as either "0", "mm/dd/yyyy" or "dd/mm/yyyy" all works now. My original format was ddmmyyyy (without slashes) and that is what appeared to be struggling in the conversion, sometimes choosing American or British based upon the validity of the date in the format (i.e. can't be 13+ months therefore must be British).

    If, as you say, using Format converts the number to text, I am surprised it still checks that one is smaller than the other, but it appears to work fine so I won't fiddle too much.

    Thank you

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If, as you say, using Format converts the number to text, I am surprised it still checks that one is smaller than the other, but it appears to work fine so I won't fiddle too much.
    I would recommend checking a bunch of values to make sure it always works the way you want to.
    I imagine this sort of format would work: yyyymmdd (year first, then month, then day), but others day not.

    For example, if the FORMAT function return the date in ddmmyyyy format, 01012012 would come before 02012010 in a text comparison.

  7. #7
    Markb384 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    44
    You are very correct JoeM, I hit a hurdle with 08/02/2014 (08022014) when compared to 11/01/2014 (11012014).

    Changing the format to "0" seems to have vaulted over this hurdle like an Olympian.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are very correct JoeM, I hit a hurdle with 08/02/2014 (08022014) when compared to 11/01/2014 (11012014).

    Changing the format to "0" seems to have vaulted over this hurdle like an Olympian.
    Glad it worked out, but that wasn't quite what I was suggesting. I was just suggesting that to verify they are date fields and to verify there values are what we expected.

    If that did work out, the date comparison should work by comparing your dates without any formatting at all (just a straight up date compare), i.e.
    Code:
    ... AND Results_TO10.[Survey Date] < sdate ...
    I am curious, does that work too?

  9. #9
    Markb384 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    44
    Oh sorry, I thought that was a suggestion It appears to work though

    Removing all formats also returns no values to my temp table, but no error message etc. That's why I started trying to use format to force the issue.

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Well, I guess go with whatever works!

    It is hard for me to test the issue, since I don't have the dueling US/UK formats to deal with.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Matching Date Formats for Query in a form
    By WickidWe in forum Queries
    Replies: 1
    Last Post: 01-15-2014, 03:19 PM
  2. Import from Excel file fails because of date formats
    By wardw in forum Import/Export Data
    Replies: 1
    Last Post: 01-01-2014, 02:20 AM
  3. Import Excel and date formats
    By thart21 in forum Import/Export Data
    Replies: 1
    Last Post: 04-19-2011, 03:49 PM
  4. Queries; need assistance changing formats
    By 9944pdx in forum Queries
    Replies: 4
    Last Post: 01-28-2011, 05:56 PM
  5. Replies: 2
    Last Post: 08-17-2010, 01:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums