[AccessD] Date Comparison Problem

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com