Changing Date Formats in SQL Query Not Working

[Markb384]

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...

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

[JoeM]

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.

[Markb384]

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

[JoeM]

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")

[Markb384]

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

[JoeM]

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.

[Markb384]

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.

[JoeM]

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.

... AND Results_TO10.[Survey Date] < sdate ...

I am curious, does that work too?

[Markb384]

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.

[JoeM]

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.