Date in query flipping to US format resulting in incorrect results.

[Robyn_P]

I am stumped. I had a lovely bit of code working and now it suddenly isn't.

I think I've located the problem, DueDate is in British format dd/mm/yyyy. When I add the day to it, this works but the SQL query comes back with results as if the date was in US format mm/dd/yyyyy. It was working before because I was testing it on the 29th so the date format was forced to stay British but now it could be either way round it's flipping it to US. I have tried updating my code to format the date but this is making no difference. Please can anybody help?? I'm meant to be demoing this in two hours. Hmm.....

Function isHistoricMateRequest(ConnectorID As Integer, DueDate As Date) As Boolean  

  ' Checks to see if there are any completed mates after the due date of the current request       
Dim rs As DAO.Recordset    
Dim strWhere As String     

  ' Add a day  to the due date.   
 ' The due date has the format dd/mm/yyyy so defaults to dd/mm/yyyy 00:00:00    ' meaning any mates performed on the same day result it it's clasification as a historic request.   
 ' To counter this, add a day to the due date. 
   DueDate = DateAdd("d", 1, DueDate)    

    strWhere = "WHERE [Connector1]=" & ConnectorID & " AND [DatePerformed] > #" & Format(DueDate, "dd/mm/yyyy") & "#"    

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryMatesFullHistory " & strWhere)    

    If rs.BOF And rs.EOF Then        
isHistoricMateRequest = False  
  Else      
  isHistoricMateRequest = True  
  End If

End function

[Minty]

You have to force the date to US format (or better still true international format) to make it work.
Review http://allenbrowne.com/ser-36.html

Try

Function isHistoricMateRequest(ConnectorID As Integer, DueDate As Date) As Boolean    ' Checks to see if there are any completed mates after the due date of the current request    
    Dim rs As DAO.Recordset
    Dim strWhere As String        '
    'Add a day  to the due date.
    ' The due date has the format dd/mm/yyyy so defaults to dd/mm/yyyy 00:00:00
    ' meaning any mates performed on the same day result it it's clasification as a historic request.
    ' To counter this, add a day to the due date.
    
    DueDate = DateAdd("d", 1, DueDate)
    
    strWhere = "WHERE [Connector1]=" & ConnectorID & " AND [DatePerformed] > #" & Format(DueDate, "yyyy/mm/dd") & "#"
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryMatesFullHistory " & strWhere)
    If rs.BOF And rs.EOF Then
        isHistoricMateRequest = False
    Else
        isHistoricMateRequest = True
    End If
    
End Function

[Robyn_P]

Thanks! I literally just figured that out. Is there an advantage of using yyyy/mm/dd over mm/dd/yyyy? Also I assume I don't have to worry about formatting the actual data. That will know what format the date is in right?

[Minty]

Access in the query editor handles the dates correctly based on your regional settings.
However in VBA , in the SQL query they are always treated as being in US format.

Personally I find the yyyy/mm/dd format is purely easier to spot any mistakes when you are debugging.
It's not obvious if 03/04/2018 is April 3rd or March 4th in a debug window but 2018/04/03 is definitely April 3rd.

This also translates well into SQL server '2018-04-03' would always work if you upgraded your back end database.

[Robyn_P]

Thanks, that's great. Okay, now to check where else I may have this problem. Unlikely to upgrade the back-end but good to keep in mind if I do

[Minty]

If you write lots of SQL on the fly in VBA you can use the SqlDate() function that Allen Browne provide on his website, it is reliable and pretty foolproof.

[Robyn_P]

Is that the one copied into this thread? https://access-programmers.co.uk/for...d.php?t=227968 I tried that, the formatting came out weird, but I was only taking snippets of it so maybe that's why. Also possibly a stupid question, but how does it know what format the date is in in the first place? Is that based on regional settings?

[Minty]

Very similar- I've just changed it a bit;

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd hh\:nn\:ss\#")
        End If
    End If
End Function

Yes it interprets your date in vba correctly, but when you pass it to the SQL engine it doesn't know about your regional settings so has to make an assumption, it always assumes it's a US date, unless it knows it can't be e.g. 12/04/2018, SQL will think it is Dec 4th , however 13/04/2018 will be interpreted as April 13th, as it couldn't be anything else.
Damn confusing isn't it ...

[Samson]

I've been at this for a couple of days now, but I think I may have solved the problem of writing dates back to the database when using UK formatting and the date changing to US format.

Once you have created your new date in UK format, before writing it to the database convert it to US format and use that format in the SQL statement, like this:

Private Sub btnUpdate_Click()
    Dim dteCurrentDate As Date, dteNewDate As Date, dteUSDate As Date

    'Get current date
        dteCurrentDate = DLookup("CurrentDate", "tblDate", "ID=1")

        MsgBox "Current Date: " & dteCurrentDate

    'Calculate new date
        dteNewDate = DateAdd("ww", 1, dteCurrentDate)

        MsgBox "New Date: " & dteNewDate

    'Convert date to US format for SQL statement
        dteUSDate = Format(dteNewDate, "mm/dd/yyyy")

        MsgBox "US Date: " & dteUSDate

    'Update current date
        DoCmd.RunSQL ("UPDATE tblDate SET CurrentDate = #" & dteUSDate & "# WHERE ID = 1")

    'Refresh form
        Me.Refresh

End Sub

If you now have a date that could be either US or UK format and you region is UK, the date is written in US format but displayed in UK format.

Hope this helps anybody else that has been banging their head on a wall for days!

[Welshgasman]

Only that last variable is a Date type, the rest are Variants?
You must explicitly define each one.

[Welshgasman]

Found this years ago when I was using Access in the office. Place in a general module.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.

Just use as Format(YourDate, strcJetDate) in any SQL string

[Samson]

Only that last variable is a Date type, the rest are Variants?
You must explicitly define each one.

Sorry, I don't understand what you mean.
The date variables are defined in the Dim statement at the top of the code.
Is that what you mean? I'm confused!

[CJ_London]

Perhaps a bit of reading would be beneficial?

https://learn.microsoft.com/en-us/of...ring-variables

[Samson]

Ah! You learn something new every day. Thanks.