469,344 Members | 5,642 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Recordset.FindFirst Multiple Criteria Not working

Having trouble with FindFirst method with AND operator.

Expand|Select|Wrap|Line Numbers
  1. Dim dblADID as Double
  2. dim intSID as Integer
  3. dim db as Database
  4. dim rstE as Recordset
  5.  
  6. set rstE = db.openrecordset("UAInfo", dbOpenTable)
  7.  
  8. dblADID = 123
  9. intSID = 324
  10.  
  11. With rstE
  12.   .FindFirst "(cdbl([ADID]) = " & cdbl(dblADID) & ") AND (cint([SID]) = " & cint(intSID) & ")"
  13.  Debug.Print !ID
  14.  
  15. If .nomatch then 
  16.  'do this
  17. Else
  18.  'do this
  19. End If
  20. 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).
Jan 23 '16 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim dblADID as Double
  2.  dim intSID as Integer
  3.  dim db as Database
  4.  dim rstE as Recordset
  5.  dim sWhere as String
  6.  
  7.  set rstE = db.openrecordset("UAInfo", dbOpenTable)
  8.  
  9.  dblADID = 123
  10.  intSID = 324
  11.  
  12.  With rstE
  13.    sWhere = "(cdbl([ADID]) = " & cdbl(dblADID) & ") AND (cint([SID]) = " & cint(intSID) & ")"
  14.    .FindFirst sWhere 
  15.    Debug.Print sWhere 
  16.  
  17. ...
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:
Expand|Select|Wrap|Line Numbers
  1. 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.

4 3006
jimatqsi
1,260 Expert 1GB
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
Jan 23 '16 #2
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.
Jan 24 '16 #3
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim dblADID as Double
  2.  dim intSID as Integer
  3.  dim db as Database
  4.  dim rstE as Recordset
  5.  dim sWhere as String
  6.  
  7.  set rstE = db.openrecordset("UAInfo", dbOpenTable)
  8.  
  9.  dblADID = 123
  10.  intSID = 324
  11.  
  12.  With rstE
  13.    sWhere = "(cdbl([ADID]) = " & cdbl(dblADID) & ") AND (cint([SID]) = " & cint(intSID) & ")"
  14.    .FindFirst sWhere 
  15.    Debug.Print sWhere 
  16.  
  17. ...
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 25 '16 #4
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:

Expand|Select|Wrap|Line Numbers
  1. "With rstE
  2. strWhere = "(([ADID]= " & dblADID & ") and ([SID]= " & intSID & "))"
  3. .findfirst strWhere
  4. End With

Thank you!
Jan 26 '16 #5

Post your reply

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

Similar topics

1 post views Thread by Brendan Wolf | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.