Multi-field search

[SueBK]

I want to set up a very simple database with a series of unrelated tables, to keep information that we use on a daily basis at our finger tips. As an example of one of the tables, I want to track legislation. My table will have six fields: name, year, jurisdiction, abbreviation, link to online copy, current (y/n).

I know how to set up a search for each field, but I would like to allow for any text to be put in a single box and return any entries against their search. This is: my search form would have a single box (possibly plus a check box for currency). If the user put in "Environment" it would search both the name and the abbreviation fields. If they put in "1994" it would search against the year field. If it's possible it just seems tidier for the user.

In the meantime, I'll design it with a search box for each field.

 

[Galaxiom]

One simple way to query multiple fields is to concatenate all the fields to be searched and test for the search string with the InStr() function.

It might be a good idea to add a delimiter character between the fields in the concatenation so that false positives are avoided when the end of one field and the beginning of the next match the search.

The downside is it is potentially slow because it doesn't use indexes at all. The alternative is to generate a custom query in a loop that adds all the fields to be searched. This canuse the indexes for whole values and the beginnings of strings.

 

[SueBK]

Found an answer that works beautifully:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663
And I didn't even have to think - just copy and paste! Gotta love it.

 

[Galaxiom]

And I didn't even have to think - just copy and paste! Gotta love it.

I would strongly discourage that practice. Thoughlessly pasting code that you have not reviewed and understood at least to some extent can lead to disaster when it doesn't work precisely as you have assumed.

You might get way with it many times but one day it will bite you or, worse still, your clients who will bite you harder still and have biggest teeth of all.

There is no substitute for thinking in devlopment.

 

[SueBK]

I didn't mean "not have to think" as in "turn off my brain completely", but rather being able to eliminate the colourful muttering and brain strain that accompanies the missing or misplaced comma or bracket that is so common when I'm coding.
I have quite a reputation with my 'cell mates' for talking to myself. They all know that more risque language is only triggered by working on databases.