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

Recordset Findfirst with two criteria does not work

P: 50
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:
Expand|Select|Wrap|Line Numbers
  1. Me.Recordset.FindFirst "Active = True"
  2. Me.Recordset.FindFirst "FullName Like '*" & txtNameSearch & "*'"
Thanks much for your help,

Alex
Feb 27 '15 #1

✓ answered by Rabbit

I'm not sure how your criteria got so far away from your original code. But this is what you need.
Expand|Select|Wrap|Line Numbers
  1. StrCriteria = "FullName Like '*" & txtNameSearch & "*' And [Active] = True"

Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,430
I see two separate criterias, where's the one where you attempted to concatenate them?
Feb 27 '15 #2

P: 50
Rabbit,

Thanks for your help, as stated I have tried many combinations, with and with out brackets, siuble quites in different areas, single quotes, etc... Inhave tried one in front of the other, same as above
Joined by "&" and by "and" with no success. I have also trued without success:

Dim rst as dao.recordset
Dim atrCriteria as String
StrCriteria = "[FullName='" & '*" & txtNameSearch & "*'" AND [Active] is True
rst.Findfirst strCriteria

Maybe I'm going about it the wrong way, but my ultimate goal is to perform a search of the FullName field supplied by searchbox, txtNameSearch, but only if the record's Active (checkbox) field is True...

Again your help us GREATLY appreciated,

Alex
Feb 27 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
I'm not sure how your criteria got so far away from your original code. But this is what you need.
Expand|Select|Wrap|Line Numbers
  1. StrCriteria = "FullName Like '*" & txtNameSearch & "*' And [Active] = True"
Feb 27 '15 #4

P: 50
Hi Rabbit, I had to make a slight mod to it, but it works like champ!, Thanks so much... I had been trying and changing things and researching for weeks, I guess it got messed up from there... In any case, I went back to my original code and that is where your suggestion worked perfectly in short I used:

Expand|Select|Wrap|Line Numbers
  1. Me.Recordset.FindFirst "FullName Like '*" & txtNameSearch & "*' And [Active] = True"
Thanks again for your help, really appreciate it, sure I'll be by again as more projects arrive.

Cheers!
Mar 2 '15 #5

NeoPa
Expert Mod 15k+
P: 31,769
Alex, if you consider that the criteria you want is a string value passed to SQL to interpret then your first rule is to write code that results in a string value.

The fact is that your string and Rabbit's string are actually identical. You simply pass it to the FindFirst immediately where with Rabbit's code you'd pass the variable like so :
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "FullName Like '*" & txtNameSearch & "*' And [Active] = True"
  2. Me.Recordset.FindFirst strCriteria
One small benefit of the way Rabbit does it is that you can then view the contents of the string to see the intended value after it's been calculated and can thus check that it's exactly as anticipated. It's often recommended that people program that way for anything SQL based for that reason. Especially for those still relatively new to the skill.
Mar 2 '15 #6

P: 50
@Rabbit
Hi Rabbit,

Sorry to bug ya with this again, but I've just been asked to add 7 more fields to this search string, AssetTag1, AssetTag2, AssetTag3, etc... But I cannot get it to work, I have tried placing double and single quotes as above around them, tried both the and/or, but no luck, I think I have the syntax wrong...

Please help, in advance thanks a mill,

Alex
Mar 30 '15 #7

Rabbit
Expert Mod 10K+
P: 12,430
You haven't posted any of your code. Kind of hard to tell you what's wrong with the code when there's no code to look at.
Mar 30 '15 #8

P: 50
@Rabbit
It's the same code you posted above, sorry about that... Although I have since figure out the issue and I got it working.

Thanks again for your usual help,

Alex
Mar 30 '15 #9

Post your reply

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