Using AND operator with FindFirst method

[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!

[puppydogbuddy]

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!

Try this:

.FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
& "And [Date] = #" & StartDate & "# "
 

[puppydogbuddy]

Try this:

.FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
& "And [Date] = #" & StartDate & "# "
 

oops! forgot a space before the And:

.FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
& " And [Date] = #" & StartDate & "# "
 

[waltvw]

Thanks a lot, it now works!

[puppydogbuddy]

Thanks a lot, it now works!

You are most welcome. Glad I could help.

[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!

[puppydogbuddy]

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!

To the best of my knowledge, the FindFirst method can have compound conditions. If .NoMatch is not working, it might be because the wrong syntax is being used for the data type.

For example, the syntax of your FindFirst statement presents CustID as a text data type:

FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
& " And [Date] = #" & StartDate & "# "
 

If CustID is a numeric data type, the syntax should have been as follows:

FindFirst "[CustID] = " & rstOtherTBL!CustID  _
& " And [Date] = #" & StartDate & "# "
 

If syntax of the CustID is not the problem, let me know and I will look into the FindFirst method. .

[puppydogbuddy]

Here is a link to another thread on this Forum that supports the fact that the FindFirst can be used with 2 variables. See response from Allen Browne, Microsoft MVP Expert.

http://www.thescripts.com/forum/thread204685.html