By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 748 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Using AND operator with FindFirst method

P: 7
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!
Jun 4 '07 #1
Share this Question
Share on Google+
7 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. .FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
  2. & "And [Date] = #" & StartDate & "# "
  3.  
Jun 5 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Try this:
Expand|Select|Wrap|Line Numbers
  1. .FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
  2. & "And [Date] = #" & StartDate & "# "
  3.  
oops! forgot a space before the And:
Expand|Select|Wrap|Line Numbers
  1. .FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
  2. & " And [Date] = #" & StartDate & "# "
  3.  
Jun 5 '07 #3

P: 7
Thanks a lot, it now works!
Jun 5 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
Thanks a lot, it now works!
You are most welcome. Glad I could help.
Jun 5 '07 #5

P: 7
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!
Jun 5 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. FindFirst "[CustID] = ' " & rstOtherTBL!CustID & " ' "  _
  2. & " And [Date] = #" & StartDate & "# "
  3.  
If CustID is a numeric data type, the syntax should have been as follows:
Expand|Select|Wrap|Line Numbers
  1. FindFirst "[CustID] = " & rstOtherTBL!CustID  _
  2. & " And [Date] = #" & StartDate & "# "
  3.  
If syntax of the CustID is not the problem, let me know and I will look into the FindFirst method. .
Jun 5 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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
Jun 6 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.