Recordset.FindFirst Multiple Criteria Not working

[ConfusedVBA]

Having trouble with FindFirst method with AND operator.

Dim dblADID as Double
dim intSID as Integer
dim db as Database
dim rstE as Recordset
 
set rstE = db.openrecordset("UAInfo", dbOpenTable)
 
dblADID = 123
intSID = 324
 
With rstE
  .FindFirst "(cdbl([ADID]) = " & cdbl(dblADID) & ") AND (cint([SID]) = " & cint(intSID) & ")"
 Debug.Print !ID
 
If .nomatch then 
 'do this
Else
 'do this
End If
End With

ADID & SID are fields in the recordset.

Each criteria works with .findfirst separately, but not when combined with the AND operator. I've tried everything I can think of, changing the syntax, converting to different data types. Nothing works. No idea why. Both Fields are number fields.

The !ID is always the first record ID. .NoMatch doesn't recognize as NoMatch, it fires as a match, even though it isnt. It always goes to the else of the if statement.

Edit: Added additional code.
UAInfo is a table. Technically a web table (sharepoint list).

[jimatqsi]

Can you show the rest of your code? What I see looks okay but there's a lot of important stuff missing. Whats rstE? Where's the NoMatch test? Maybe add .Debug Print each of those variables to be sure what's being passed in the criteria. I usually build the criteria separately, in a string, so that criteria string can be examined, if needed.

Jim

[ConfusedVBA]

I added some additional code. The rest of the code works, which is why I left it out. And .findfirst works with one criteria, but as soon as I add the second criteria it stops working entirely. No errors thrown though.

I did a debug.print on the criteria instead of setting it to a string, and it looks fine to me as well, and the variables are set.

Access 2010/Sharepoint 2010.

[jforbes]

Usually, the next step in troubleshooting something like this is to take do some Debug Magic to find out what a live version of the Where Clause looks like, then copy and paste it into a Query to see what it returns.

I would add a String Variable to stuff the Were Clause into before using it (like Jim advises), that way it can be inspected/printed:

Dim dblADID as Double
 dim intSID as Integer
 dim db as Database
 dim rstE as Recordset
  dim sWhere as String
 
 set rstE = db.openrecordset("UAInfo", dbOpenTable)
 
 dblADID = 123
 intSID = 324
 
 With rstE
    sWhere = "(cdbl([ADID]) = " & cdbl(dblADID) & ") AND (cint([SID]) = " & cint(intSID) & ")"
    .FindFirst sWhere 
    Debug.Print sWhere 
 
...

When you run the code, the entire Where Clause will be printed to the Immediate Window. You can then copy this onto the Clipboard, open a new Query, add the table "UAInfo" then switch to SQL View and paste in the Where Clause to get something like this:

SELECT * FROM UAInfo WHERE (cdbl([ADID]) = 1) AND (cint([SID]) = 1)"

then try to execute it an see if there are any results or errors.

[ConfusedVBA]

Great Advice.

The issue I found using your method is that cdbl() and cint() are setting the field name to Null, which results in a "Invalid use of Null" error. I removed the functions from the string entirely, and it found the record. Oddly, using a single criteria, these functions work just fine and dont give an error, which makes me think it might be syntax related, but thats not something I'm concerned about now.

The final part of code which worked was:

"With rstE
strWhere = "(([ADID]= " & dblADID & ") and ([SID]= " & intSID & "))"
.findfirst strWhere
End With

Thank you!