Using .Find with multiple search criteria

[salzan]

I have this code:

strAny = "[YearId] = " & rsTemp!YearId
rsPerm.Find strAny

and it works. However, when I do the following it doesn't

strAny = "[YearId] = " & rsTemp!YearId & " AND " & _
"[DeptId] = " & rsTemp!DeptId & " AND " & _
"[CatId] = '" & rsTemp!CatId & "'"
rsPerm.Find strSQL

Can anyone tell me what I'm doing wrong? I should add YearId and DeptId are numeric and CatId is String.

Greatly appreciated.
Salzan

[puppydogbuddy]

I have this code:

strAny = "[YearId] = " & rsTemp!YearId
rsPerm.Find strAny

and it works. However, when I do the following it doesn't

strAny = "[YearId] = " & rsTemp!YearId & " AND " & _
"[DeptId] = " & rsTemp!DeptId & " AND " & _
"[CatId] = '" & rsTemp!CatId & "'"
rsPerm.Find strSQL

Can anyone tell me what I'm doing wrong? I should add YearId and DeptId are numeric and CatId is String.

Greatly appreciated.
Salzan

Salzan,
Try changing this: rsPerm.Find strSQL

To this: rsPerm.Find strAny

[salzan]

Sorry that was a typo - it's strAny. The variable name is not it.

[FishVal]

Hi, Salzan.

Do you mean ADODB.Recordset ?
If so then this may give an answer to your question.

Regards.
Fish

[puppydogbuddy]

Hi Fish,

And this link compares the Find methods of DAO with ADO.........

http://msdn2.microsoft.com/en-us/library/aa165283(office.10).aspx

[salzan]

I should have specify that I'm using ADO Recordset. The articles didn't help. My problem is with the criteria string. When I specify only one criteria it works but when I specify multiple criteria with AND operator it give me an error message "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". I've checked the types of the arguments and they're correct. I don't know what else to search.

Thanks you for your help.
Salzan

[puppydogbuddy]

I should have specify that I'm using ADO Recordset. The articles didn't help. My problem is with the criteria string. When I specify only one criteria it works but when I specify multiple criteria with AND operator it give me an error message "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". I've checked the types of the arguments and they're correct. I don't know what else to search.

Thanks you for your help.
Salzan

Try it this way and see if it helps:

strAny = "[YearId] = " & rsTemp!YearId & _
" AND [DeptId] = " & rsTemp!DeptId & _
" AND [CatId] = '" & rsTemp!CatId & "'"

[Scott Price]

According to the Access help file, the .Find method of the ADO recordset only supports one column searches. In other words, you are out of luck trying to set multiple column criteria!

In the VBA code editor window, position your cursor in the word Find and press F1, this brings up the help file with this specific information.

Regards,
Scott

[salzan]

THANK YOU ALL. Atleast I know I'm not doing it wrong. I think I'll create a compound column, concatenate the values together, and search against it. It should accomplish what I'm after.