Using AND operator with FindFirst method

Originally Posted by waltvw

I'm using FindFirst method in Access VBA to find a particular record in a recordset. I have 2 search criteria each of which works just fine if used separately as an argument in FindFirst, but NOT in combination with one another. I tried various combinations of quotes, double quotes, brackets and paranthesis but in case of using 2 conditions I'm still receiving the error message saying "run-time error 13 - type mismatch". If I use just one of them then there are no error codes, so it looks like I simply can't remember the right syntax, i.e. there aren't, in fact, any data type mismatches.

This is the code that doesn't work:

Set rst = dbs.OpenRecordset("tblCustomer", dbOpenDynaset)
With rst
.FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' " And _
"[Date] = #" & StartDate & "# "
'Other Statements
End With

However, these 2 pieces of code work just fine:

Set rst = dbs.OpenRecordset("tblCustomer", dbOpenDynaset)
With rst
.FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "
'Other Statements
End With

Set rst = dbs.OpenRecordset("tblCustomer", dbOpenDynaset)
With rst
.FindFirst "[Date] = #" & StartDate & "# "
'Other Statements
End With

Anybody knows how to resolve it?

Thanks a lot in advance!

Originally Posted by puppydogbuddy

Try this:

Originally Posted by waltvw

Thanks a lot, it now works!

Originally Posted by waltvw

It works but I just discovered that either Date or CustID is not taken into consideration in the evaluation, i.e. only one condition can actually be used in FindFirst as opposed to "Where" clause in SQL statement that can use multiple AND's & OR's. This is because my next statement, If .NoMatch evaluates to True even if there are records in the table that has a combination of CustID and Date which have been evaluated by .FindFirst statement. If that's the case, I'm still OK, 'cause before I got your response on how to fix the syntax in question I had created some logic in the loop to get around the issue.

Thanks again!