473,232 Members | 1,457 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,232 software developers and data experts.

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 3591
jimatqsi
1,271 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

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

Similar topics

2
by: @ndy | last post by:
Hey, this is the situation. I have imported a table from Excel and know i must edit the saldo in the corresponding records tbl_Lignes. Impdata tbl_Lignes...
4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
3
by: wvhines | last post by:
Hello: I am an ambitious novice...I have a main form "frmGeneralContracting" that has a subform on it called "frmsubDeliverables" and I am trying to restrict editing if the current user's login...
3
by: developing | last post by:
Hello How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field) edit: this will be in the after...
1
by: akirekab | last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria. Here is sample of what i need. =DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
1
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
4
by: DANNYOCEAN | last post by:
I have a code that sucessfully calculates how many numbers are lower than for example 45 , for large sample number list. But im looking for a code that have a COUNT function with MULTIPLE...
12
Seth Schrock
by: Seth Schrock | last post by:
I have a search form that allows the user to type in a customer name and it brings up all the records that are like the name the user typed in. The user then just double clicks on the record and it...
8
by: alexrubio | last post by:
Both of the following statements work perfeclty when used separaty. I have tried a gazillion different combinations to concatenate them and Can't get It to work, driving me insane:...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.