QueryByForm, search for records with date field inbetween a range... code not working

[Sharky II]

Hi guys, sorry for the long (winded) title.

I'm using query by form as my main search method. I want to search for a range of dates so, for example you can bring up all customers whose membership expires between those two dates.

I'm using the following code:

If Not IsNull(Me![SrchMembershipExpiryDateEnd]) Then
    If Not IsNull(Me![SrchMembershipExpiryDateStart]) Then
        where = where & " AND Me![MembershipExpiryDate] between #" + _
        Format(Me![SrchMembershipExpiryDateStart], "dd/mm/yyyy") + "# AND #" & Format(Me![SrchMembershipExpiryDateEnd], "dd/mm/yyyy") _
        & "#"
    Else
        where = where
    End If
End If

I am trying to use the format method so i don't get problems with the usa/uk date thing... i want it in uk format only! I'm getting wierd problems and i think it's down to the fact that i haven't done the formatting thing correctly?

Sometimes it doesn't find records that it should. I assume it's swapping the day/month values like american dates. I'm getting it to print a message box out of what the dates are and they seem correct.

Can anyone tell me what i'm doing wrong?

Thanks again guys

Eddie

 

[Sharky II]

fixed it... i think. My problem is that i was formatting it into UK style... when i should actually be converting it from uk to usa style, as that's what it recognises...

so i changed it to mm/dd/yyyy

I'm pretty sure that's correct... can anyone confirm?

Cheers