Recordset Findfirst with two criteria does not work

[alexrubio]

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:

Me.Recordset.FindFirst "Active = True"
Me.Recordset.FindFirst "FullName Like '*" & txtNameSearch & "*'"

Thanks much for your help,

Alex

[Rabbit]

I see two separate criterias, where's the one where you attempted to concatenate them?

[alexrubio]

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

[Rabbit]

I'm not sure how your criteria got so far away from your original code. But this is what you need.

StrCriteria = "FullName Like '*" & txtNameSearch & "*' And [Active] = True"

[alexrubio]

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:

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!

[NeoPa]

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 :

strCriteria = "FullName Like '*" & txtNameSearch & "*' And [Active] = True"
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.

[alexrubio]

@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

[Rabbit]

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.

[alexrubio]

@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