FindFirst selecting records where a date field is >= system date

[Louverril]

I am trying to find the first record in a table with an expiry date greater
than the current system date, but I can't get the correct findfirst syntax.
I'm using Uk date format for everything - system date is set to dd/mm/yyy.

See problem line below:

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("Select * from tblCheck where Activated = " &
True)

With rec
.FindFirst "Expiry_Date >= " & Now '''PROBLEM LINE
If .NoMatch Then
fCheckExpiryDate = False
ElseIf !Expiry_Date >= Now Then
fCheckExpiryDate = True
End If
End With

What is the correct syntax for the findfirst (and does the set rec statement
look correct?)

Have also tried:
Dim TodaysDate As Date
TodaysDate = Now
.FindFirst "Expiry_Date >= #" & Format(TodaysDate, "mm\/dd\/yyyy") & "#"

And

.FindFirst ("Expiry_Date >= #" & Format(Now, "yyyy-mm-dd hh:nn:ss") & "#")

And

.FindFirst "Birthday >= #" & Now & "#"

Thanks Lou

[Dale Fye]

How do you know you don't have the right syntax? Are you getting an error,
or are you not getting the record back that you expect? What do you want to
do with it once you have found it? Based on your code segment, all you want
to do is itedentify that it exists.

If you are trying to locate the record with the smallest expiration date >=
today (and if you have multiples, don't care which one you get), then I would
modify then, you should be able to do the following to identify the record
you want.

Dim db As Database
dim strSQL as string
Dim rec As DAO.Recordset

Set db = CurrentDb
strSQL = "SELECT Top 1 * FROM tblCheck " _
&"WHERE Activated = -1 " _
& " AND [Expiry_Date] >= #" & Date() & "# " _
& "ORDER BY [Expiry_Date]"
Set rec = db.OpenRecordset(strSQL)

IF rec.eof then
fCheckExpiryDate = False
Else

fCheckExpiryDate = True
End If

rs.close
set rs = nothing

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[Louverril]

Thanks Dale but I really need to know now the syntax for a findfirst. I
can't even use findfirst using any variable name. All the examples are for
me.myfield. I haven't got any form fields.

So I can't check whether my name exists in a simple table

myname= allyson
.FindFirst "[name] = #" & myname & "#"

or whatever the syntax is. It's driving me crazy.

The MS Access says this about findfirst and dates
You should use the U.S. date format (month-day-year) when you search for
fields containing dates, even if you're not using the U.S. version of the
Microsoft Access database engine; otherwise, the data may not be found. Use
the Visual Basic Format function to convert the date. For example:

Visual Basic for Applications
rstEmployees.FindFirst "HireDate > #" _
& Format(mydate, 'm-d-yy' ) & "#"

So I tried this format .FindFirst "Birthday > #" & Format(TodaysDate,
"m-d-yy") & "#"
but it gets the same error.

Error Number 3251, Operation is not supported for this type of object.

[Dale Fye]

Allyson,

A common use of the FindFirst method is to find a record for a particular
form. As an example, if you have an employees table, and a form for
entering employee data (frm_Employees), you might add a textbox in the forms
header to search for a lastname. In the after update of that event, you
would have some code that looks like the following:

Private Sub txt_FindLastName_AfterUpdate

Dim rs as dao.recordset
set rs = me.recordsetclone

rs.findfirst "[LastName] = '" & me.txt_FindLastName & "'"
if rs.nomatch then
msgbox "match not found"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

End sub

The onl;y real difference between this and your code is to replace the pound
sign (#) with a apostrophe ( ' ).

If you were going to look for a date, you would use the # as a delimeter.

Hope this helps
Dale

"Louverril" <Louv...@discussions.microsoft.com> wrote in message
news:2F6B7B0E-F2D4-4F36...@microsoft.com...

[Louverril]

Dale,

Thanks very much for this. I had real difficulty finding an example of the
syntax for variables in a findfirst statement.

Regarding the date issue your code did not work because I use the UK date
format - dd/mm/yyy . Even thoughI use this throughout it appears that in SQL
you have to convert the system date to US format in order to compare it. So
I had to use

& "WHERE [Expiry_Date] >= #" & Format(todaysdate, "mm\/dd\/yyyy") & "#"

See this form Microsoft findfirst VBA help - but I couldn't actually get
this code to work.

"You should use the U.S. date format (month-day-year) when you search for
fields containing dates, even if you're not using the U.S. version of the
Microsoft Access database engine; otherwise, the data may not be found. Use
the Visual Basic Format function to convert the date. For example:

rstEmployees.FindFirst "HireDate > #" _

& Format(mydate, 'm-d-yy' ) & "#"

It would be useful if this was publicised more by Microsoft. I was driving
myself crazy. Yesterday night trying to solve this problem!

Thanks again for you help.

Lou