[AccessD] Date Comparison Problem

Hi Charlotte

But all the clumsy code with Replace? Tell me you didn't write it.

In our applications when I need a date I convert whatever value that may be to variables of date/time value. If that fails there isn't much to do.
The only variation to that is when you have to deal with non-existing dates, Nulls, as only a Variant will hold that.

Also, don't confuse the novices: CDate() does not convert expressions to a "system format" but to a date/time value. It fails for Null, but if you need to pass Null to a varDate holding Null or a date, use good old CVDate().

/gustav

>>> cfoust at infostatsystems.com 30-03-2006 20:39 >>>
Maybe in *your* applications, Gustav! LOL  Our "dates" can be in
multiple date and type formats and may be dates, strings or nulls (if
we're lucky).   The Cdate converts them all to the system format so
we're working with apples instead of fruit salad. We also have to handle
time strings without a date attached and a variety of other cute
oddities.  That's why it goes the long way around, we wanted both
insurance and code that a novice could plow through if necessary. ;o>


Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, March 30, 2006 10:26 AM
To: accessd at databaseadvisors.com 
Subject: Re: [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