Criteria for Yes/No Fields

[Max]

Acc 2K. Doing a FindFirst where I want to use two fields
as Criteria: [Complete] is a Yes/No field and [OptNo] is
a number field. I have: rst.FindFirst "[Complete] ='" &
False & "' and [OptnNo]= " & OptNoControl
This is giving me an error. What is the correct syntax?
Thanks in advance.
Max

 

[Ken Snell [MVP]]

A "True/False" field does not store the actual words True or False. It
stores a value of 0 (false) or -1 (true). The instrinsic constants True and
False are substitutes for these actual number values. Thus, try this:

rst.FindFirst "[Complete] = False and [OptnNo]= " & OptNoControl

 

[Tom Wickerath]

Hi Max,

Try something like this:

rs.FindFirst "[Complete] = " & False & " and [OptnNo]= " &
OptNoControl & ""

The above should all be on one line in your VBA code.

True and False evaluate in Access to the numbers -1 and 0
respectively. Therefore, you should not include the single
quotes wrapped around the word False. Also, your recordset
needs to be a dynaset or snapshot type recordset (ie. a
query). It cannot be a table.

The following example works in the sample Northwind
database:

Function FindProduct(UnitsInStock As Integer) As String
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryProducts")
rs.MoveFirst


'Watch for word wrap
'The next line of code is all on one line
rs.FindFirst "[Discontinued] = " & False & " And
[UnitsInStock] = " & UnitsInStock & ""

FindProduct = rs("ProductName")

Debug.Print FindProduct


ExitProc:
' Cleanup
Set rs = Nothing
Set db = Nothing
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in FindProduct Function..."
Resume ExitProc

End Function



Tom
___________________________________

Acc 2K. Doing a FindFirst where I want to use two fields
as Criteria: [Complete] is a Yes/No field and [OptNo] is
a number field. I have: rst.FindFirst "[Complete] ='" &
False & "' and [OptnNo]= " & OptNoControl
This is giving me an error. What is the correct syntax?

Thanks in advance.
Max

 

[Max]

Thanks - that worked.
Max