simple question about a function

[e2cheng]

SITUATION:

i'm trying to create a search function for a table with multiple fields.

ie. product | price | description | etc.

currently, I have textboxs that are referenced to the query. thusly, when the query is run, it takes the information from the textboxes and looks to see if any of the corresponding field values match.

however if this was an entry

product: Tickle me Elmo
price: $2
description: furry red guy

and i put in a search for
txtproduct = Elmo or txtdescription = red it won't return anything because my product = Tickle me Elmo not Elmo
and description = furry red guy not red

QUESTION: is there a function or a key word that is used the query to look for the string within the field value??

Thank you so much for your time and help. It is greatly appreciated!

 

[Daxton A.]

Try this

Instead of using text boxes. Use Combo Boxes to list the available products and descriptions and price.
And reference those in your query.

 

[e2cheng]

Thanks for the tip. I have already done that with one of my fields, but the other ones must be open to custom messages. Do you know of any key word that requests just a search for within??

Thanks again for your help!

 

[barbarossaii]

what about the Like-Operator..

Hi e2cheng,

perhaps you could use the Like-operator ?!?


For the 'columns' in your query the criteria could be:


like "*" & [txtproduct] & "*"
[txtproduct] is null



like "*" & [color] & "*"
[color] is null


The line w/ the "like" opens a box that asks for the respective inputs (because of the "[" "]"; the "like" itself searches if your input is somewhere in the 'column' and displays only the matching entries.
The line w/ the "is null" ensure that you can use both, one or the other criterium: so you can search for "red" & "Elmo", later for "red" alone and then for "Elmo" alone, when you fill both one only one box.

HTH,
Barbarossa II

 

[e2cheng]

Thank you SO much!

Hi barbarossaii

Danke! Thank you SO much for that tip. I will try it now, but I'm sure it works. Do you know where I could find all these functions? I bought a book from Microsoft, but it doesn't have anything like this in there.

Thank you again for your help!

Sincerely,
Eric

 

[e2cheng]

implementation issues

Hi, I've tried using

Criteria: like "*" & [txtproduct] & "*"
Or: [txtproduct] is null

Criteria: like "*" & [color] & "*"
Or: [color] is null

in the query.

However, upon the save, my 'Or:' statement (ie. '[txtproduct] is null' and '[color] is null') is shifted to a new column with '[...]' as the field and 'Is Null' as the 'Or:' criteria.

Will this make a difference if it works functionally???

Just to clarify as well: the "*" stands for the entire field contents right?