Search fields SQL

[noobaccess]

Hi,

Anyone know how i can write a SQL query based on search fields? i want to do up a search form that i can search for items that will narrow down the things i want to search. i've been trying to figure it out but my SQL query doesnt seems to work.

Your help will be greatly appreciated!!

[June7]

Review tutorials at http://datapigtechnologies.com/AccessMain.htm

Especially section Access Forms: Tips and Techniques:
Look the items:
Build a Custom Filter in Your Form
Creating a Basic Search Form

[noobaccess]

oh! thanks!

however, is it possible to search for values that are in a listbox? Like i have a table with a field that stores multiple data in a single field. for example, there are values stored in the table column called toys, which the values are dolls, cars, machines. if i search cars in my listbox, can it show that row of values?

[orange]

Like i have a table with a field that stores multiple data in a single field.

This indicates a relational design problem.

see
http://forums.aspfree.com/attachment...2&d=1201055452

[June7]

If these are multi-value fields then no, at least not easily. I never use multi-value fields. http://office.microsoft.com/en-us/ac...001233722.aspx

If the data is just a string of comma separated text then yes, it is possible. The example in the tutorial is applicable. Although the wildcard would have to be on both sides of the criteria: Like "*" & [critiera input] & "*"

[noobaccess]

Although the wildcard would have to be on both sides of the criteria: Like "*" & [critiera input] & "*"

Oh!! this works well together with the link that you have given me at #2 !

Really thank you all very much!!!!!

[noobaccess]

hi sorry can i ask another question?

i was asked to do "ALL" search textbox where i can search for anything in the textbox but i tried using ( Like [Forms]![Prospect Table search]![Search] & "*" ) but it doesnt work..

any idea if i have done something wrong or i am not suppose to use this query statement?

[June7]

Why doesn't work - error message, wrong results, nothing happens?

What field is that criteria under?

You have only the one wildcard. That means the input string must be at beginning of the values.

[noobaccess]

Actually i m not sure what field it should be under, i thought i shld have all the column name under that criteria so i tried using a wildcard but it doesnt seems to work. i tried putting all the column name under the criteria but it still doesnt work.

it shows an error message when i use the wildcard.. can you take a look at it ?





this is my query.. is it wrong?

i tried this too! but it doesnt work..

[June7]

Comply with the error popup. Remove the criteria from the * column. The criteria for the other fields need to be on separate OR rows. The resulting appearance will be stair-steps. As it is the AND operator is in effect which means the criteria value must be found in ALL fields for record to return.

Uncheck the search fields so the query won't have duplicates of the fields. The search will still work, those columns just won't show.

[noobaccess]

hmmm... but if i were to use OR everything in my search results will be messed up.. i just tried, it doesnt work however if i were to use AND it works perfectly fine. but the search textbox that i want to search for anything doesnt work..

[June7]

Then I don't understand what you want.

I tested a query that referenced one textbox as criteria for multiple fields. I tested both AND and OR operators. The OR worked properly and the AND failed.

Do you want both a general input textbox that will be criteria for all fields as well as specialized textboxes as criteria for respective fields? Mixing AND and OR operators is messy and frustrating.

[noobaccess]

yes i understand that! Search field is very frustrating!

hmm ok .. i am actually trying to search for records that will allow me to displays my the items that i want to search. currently i have 4 drop down list and 1 multi purpose search textbox that allows me to search for anything.

Is using OR better? Because i followed the video you show me the other time and it was using AND

this is how the OR should look like right?

[June7]

The video uses AND because each field has different criteria reference.

The image shows not all criteria with LIKE and wildcard.

[noobaccess]

for price point i could not use wildcard or LIKE because that search field is use to search for points(numeric) that are below a certain number....