[AccessD] Date Comparison Problem

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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
- Beach Access Software
Sent: Thursday, March 30, 2006 9:03 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Date Comparison Problem

Well I spoke (or wrote) a little too soon.  There's another statement,
preceding the others which works right in US date format but not in UK
format.  The statement reads:

    rstManpower.FindFirst "fldManpowerDate = #" & datCurrentDate & "#
AND " _
        & "fldSkillID = " & !fldSkillID

datCurrentDate contains 01/12/2006 and !fldSkillID = 8

If .Nomatch = True then it adds the record and cycles back to the
.FindFirst where .Nomatch should be False.  But it comes back True and
adds another record in an endless loop.  Even if I do a
rstManpower.Requery, it fails.  I looked in the Manpower table and the
records exists.

It works correctly when Windows is in US date format and fails in UK
format.

Can anyone see what's wrong with this FindFirst statement?

MTIA,

Rocky



Rocky Smolin - Beach Access Software wrote:
> Dear List:
>
> As I was writing this question a solution occurred to me.  But since I

> already spent all this time framing the question thought I'd post it 
> anyway and add the answer to the end.
>
> I have a routine which works when regional setting is set to English 
> (United States) and fails when regional settings are set to English 
> (United Kingdom) .  The difference is in a date compare.
>
> The statement reads: "If datCurrentDate > Me.txtScheduleEndDate Then"
>
> When set to United States datCurrentDate contains 12/02/2005 and 
> Me.txtScheduleEndDate contains 07/30/2006 based on making a break 
> point at that statement and hovering the mouse over the two variables.

> So the test fails, as it should.
>
> When regional setting is set to United Kindom datCurrentDate contains
> 02/12/2005 and Me.txtScheduleEndDate contains 30/07/2006 .  This time 
> the test passes which it should not.
>
> Turns out that even though the text box shows 30/07/2006 it was not 
> making the comparison correctly.  So I Dimmed another date 
> datScheduleEndDate and changed the code to read:
>
>         datScheduleEndDate = Me.txtScheduleEndDate
>         If datCurrentDate > datScheduleEndDate Then
>
>
> And now it works.
>
> Solution in search of a problem.
>
> Thanks (for making me think this through) and regards,
>
> Rocky
>
>   

--
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com

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