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