.findfirst Does Not Find Record

[TimTDP]

I need to find a range of data in a recordset. I have the following code

.FindFirst ("Reading > " & LowerLimit)
If .NoMatch Then
 MsgBox ("No Records Found!")
Else
dteStart = rstData!DateTime
End If
   
.FindFirst ("Reading <= " & LowerLimit & " and DateTime > #" & dteStart & "#")

The first .FindFirst works, but not the second one! I have figured out that it has someting to do with the dteStart criteria because
.FindFirst ("DateTime > #" & dteStart & "#") also does not work

What am I doing wrong?

Once I have the date range, how can I search for the maximum Reading in the date range using the same recordset?

Many thanks in advance

 

[gemma-the-husky]

i am not certain, but often using dates in expressions can result in them being treated as US dates

so if you have UK date 6/11/2011, it possibly will be interpreted as 11/6/2011 - which might cause the search failure.

it is worth checking this.

you can "coerce" a format with this sort of thing.

DateTime > #" & format(dteStart,"long date") & "#")

I am told that "long date" does not necessarily work in non UK domains, and there is an absolute coercion - but I am not sure exactly what that is.

 

[vbaInet]

Also try this:

.FindFirst ("Reading <= " & LowerLimit & " AND DateTime > " & Format(dteStart, "\#mm\/dd\/yyyy\#"))

And

.FindFirst ("Reading <= " & LowerLimit & " and CDbl(DateTime) > " & CDbl(dteStart))

 

[TimTDP]

American dates is indeed what is required

Thanks