FindFirst not working when using DATE field in criteria

[Praveenevg]

Hello,

I have spent more than 5 hours so far to make the FindFirst command work for a date value, and still made no progress.

Please help me on following:

For some reason FindFirst is not working whenever my search criteria constains a date field and date value. Visually, I can see a record in table with the same date I am searching for. Is there anything to consider while searching using a date field?

[John_G]

Where are you using the FindFirst - in VBA?

When searching for dates, the value must be enclosed in '#' marks, and must be in mm/dd/yyyy format. I know that's not the format used in most places, but that's what Access needs. It's not that difficult, though. A FindFirst on a recordset would look something like this:

rst.FindFirst "Datefield = #" & format(searchdate,"mm-dd-yyyy") & "#"

where Datefield is the recordset field to be searched, and searchdate is the date value you are looking for.

HTH

John

[Praveenevg]

Thanks a Ton. it worked. I spent a day so far troubleshooting this and finally your solution made it to work.

[recyan]

Do not know about FindFirst in VBA & may not be relevant to this case, all the same,
while working with "First" in Queries with Grouping, found that "Min" & "First" could throw up different results at times.

Thanks

[John_G]

recyan -

"Min" & "First" could very easily give you different values; it would depend on the sort order used in the query, or on whatever order MS Access chooses to use. "Min" will always give you the same value, but changing the sort order could change the value you get using "First"

John

[recyan]

Was wondering whether "FindFirst" in VBA & "First" in Grouping query are the same functionally.

Thanks