Searching within multiple fields in an Access

[yourtrashhere]

So basically, what I have is a bunch of words in one memo field, for
example:

dog cat cowboy tree flower

To search it, this is the code I have now.

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*" * "
AND "
End If

The only problem is what I search for needs to be "in order", for
example, if I search for dog, I'll get the table. But, if I seach for
tree, I won't because tree was not place first. Can you please help me?
Thanks a lot!
Reply With Quote

 

[Guest]

So basically, what I have is a bunch of words in one memo field, for
example:

dog cat cowboy tree flower

Then you're violating the basic rule that fields should be atomic. Have you
considered instead using Access as a relational database, and storing one
keyword PER RECORD in a related table? You'll find it a much better design
for many reasons!

To search it, this is the code I have now.

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*" * "
AND "
End If

The only problem is what I search for needs to be "in order", for
example, if I search for dog, I'll get the table. But, if I seach for
tree, I won't because tree was not place first. Can you please help me?
Thanks a lot!

The * is a wildcard which means "match any string of characters". You have
two wildcards, AFTER the txtLastName refererence; this will match the exact
string at the start, and anything afterwards (and you are using belt and
braces because you don't NEED two wildcards there).

Put another wildcard before:

If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE ""*" & Me.txtLastName & "*"" AND "

Thus if txtLastName contains cat, you'll get a criterion

[LastName] LIKE "*cat*" AND

(to be completed later in your code, I presume); and it will find records
containing any string of characters, the letters cat, and then any other
string.

Note that this will include "muscatel" and other words containing the
substring cat.

John W. Vinson/MVP

 

[yourtrashhere]

That's not really what I meant. Basically, I have 2 fields:

|NAME|Tags |
|Bob |cat dog flower|

I need to search the tags for a word. Not necessarily cat. Just
anything. The code I have will only allow me to search for cat, but I
want to be able to search for "flower" to bring up the same record.
Thanks a lot for your reply though!

 

[John Vinson]

That's not really what I meant. Basically, I have 2 fields:

|NAME|Tags |
|Bob |cat dog flower|

I need to search the tags for a word. Not necessarily cat. Just
anything. The code I have will only allow me to search for cat, but I
want to be able to search for "flower" to bring up the same record.
Thanks a lot for your reply though!

LIKE "*" & [Enter keyword:] & "*"

then. My apologies for answering the question that you asked, rather
than the question that you intended.

Note that my criticism of the structure of the Tags field still
applies. You have a one (name) to many (tags) relationship; rather
than embedding the many tags into a single field, you would do much,
much better to have a properly normalized structure:

Names
PersonID <<< Primary Key, *not* a name since names aren't unique
PersonName <<< NAME is a reserved word, Access will get confused
<other info about the person or entity>

Tags
Tag <<< list of valid tag values for use in a Combo Box

NameTags
PersonID
Tag

You'ld have records like

Names
123
Bob
<other info about Bob>

Tags
flower
cat
theramin
widget

NameTags
123 flower
123 cat
123 widget


John W. Vinson[MVP]