[AccessD] Date Comparison Problem

Actually, no, I didn't.  My boss did.  And yes, I meant the system
date/time format.  The snippet I posted is called from all over and the
user might enter anything including a long date.  Like a lot of
applications, this one has grown organically over time and I have only
been allowed to maintain and extend it, not rewrite. 


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 11:01 AM
To: accessd at databaseadvisors.com
Subject: Re: [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

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