One search box, two fields

[rlarkin]

Hello,

Is there a quick way to have a search value (Like [Forms]![Report Form]![UICodeTextBox]) look for a match in two fields (Code A, Code B)?

I've tried using the Or criteria option, but as I have many searches on the form, which only lookup one field, it seems to dramatically slow down the query to copy them all down for one Or criteria.

I'm thinking it must be possible to combine the fields at Field or Select in SQL for the purposes of a search, but my lack of experience has defeated me.

Thanks for your help,

Russell

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here will give you some pointers.

 

[rlarkin]

Hey John,

Thanks for the welcome and advice. I don't think the linked example is what I'm looking for, as the query is still using the Or criteria to search multiple fields. I'm trying to figure out if it's possible to first combine the fields into one, to then have a single Criteria, as the criteria is always the same. Something like:

Field: Field 1 and Field 2
Criteria: Like "*" & [Forms]![Form]![SearchTextBox] & "*"

The only reason for wanting to try and do it this way is that I'd prefer to have only one text box, but using Or criteria for it makes the query really slow.

 

[vbaInet]

Aircode

[CodeA] & [CodeB] LIKE "*" & [Forms]![Form]![SearchTextBox] & "*"

I don't know the performance implications with using this method but I suspect it will be slower because you've lost the power of indexing.

 

[rlarkin]

At the moment its faster compared to using an Or criteria. A few seconds compared to a few of minutes. I've only just started using queries, so I don't know if that's normal for a query with 16 fields, which all have Like criteria.

Thanks very much.

 

[vbaInet]

For a small dataset it will look faster. But as the dataset progressively gets larger, you will see the degradation in performance. You need not worry if you're not dealing with millions of records.

 

[JANR]

To add to VbaInet's suggestion you should add a field splitter so you don't get overlapping results.

[CodeA][COLOR=red] & "|" &[/COLOR] [CodeB] LIKE "*" & [Forms]![Form]![SearchTextBox] & "*"

The pipe character is hopefully NOT in either CodeA or CodeB

JR

 

[rlarkin]

I won't ever be dealing with more than a few thousand records, so this method should work fine for my purposes.

On a side, I'm wondering if having an independent query for the single Or criteria, which feeds into the main query would be faster? I'll leave it as is for now, but might test out that method at some point.

 

[vbaInet]

The pipe character is hopefully NOT in either CodeA or CodeB

One can only hope The safest one would be vbNullChar, so Chr(0)

But this isn't really necessary because an overlap won't matter in this instance since the search is pattern matching between the two fields.

 

[vbaInet]

On a side, I'm wondering if having an independent query for the single Or criteria, which feeds into the main query would be faster? I'll leave it as is for now, but might test out that method at some point.

You will need something like an Execution Plan (which isn't shipped with Access) to know which one is faster but my suspicion is this method will perform more executions than your current method.