[AccessD] Date Comparison Problem

Hi Charlotte

That's really a long way to go. One line of code will do.
CDate() is normally not needed as you should (must) have variables of date/time value before you begin building your SQL string. 
Also, use the backslash as escape character:

Public Function StrDateSQL(ByVal dat As Date) As String

' Formats full string of date/time in US format for SQL.
' Overrides local (non US) settings for date/time separators.
' Example output:
'
'   #08/16/1998 04:03:36 PM#
'
' 1999-10-21. Cactus Data ApS, CPH.

  StrDateSQL = Format(dat, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#")

End Function

Credit goes to Lembit who stressed this many years ago.

/gustav

>>> cfoust at infostatsystems.com 30-03-2006 20:11 >>>
The easiest way to handle it is to use Cdate functions around each date
before comparing them and then forcing a us format, which is what SQL
wants:  

If IsDate(varDate) Then
    strDate = Format(CDate(varDate), "mm/dd/yyyy")
    strTime = Format(CDate(varDate), "h:nn:ss AM/PM")
    If Format(Now(), "/") <> "/" Then
        ' we have a non-standard date separator
        strDate = ReplaceChars(strDate, Format(Now(), "/"), "/")
    End If
    If Format(Now(), ":") <> ":" Then
        ' we have a non-standard time separator
        strTime = ReplaceChars(strTime, Format(Now(), ":"), ":")
    End If
    
    USDate = "#" & strDate & " " & strTime & "#"
End If


Charlotte Foust