[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