Search any part of field query...

[breaker11]

I am trying to set up a query where typing a value into a dialog box will query out all fields that contain that partial value. For example, say I want to search for all fields that have 9 as the first number, how do I query it out rather than only the exact value? I hope this is clear enough. Thanks for your help.

 

[teenmonk]

I think I know exactly what you are looking for. A similar question was asked a while back ago. Please conduct a search by 'subject lines' for "Using An Access Form as a Search tool". The posting has 15 responses and was posted in 6/20/01.

Hope that helps?

-Henry

 

[CosmoKramer]

In your query, put something like the following code in criteria box for each field:

Like [Forms]![frmYourForm]![txtYourTextBox] & "*".

Just make sure you cascade the entries so the Or operator is used instead of And. This will return all rows that begin with what the user enters...

HTH

 

[breaker11]

Cosmo,
I am only needing to query one field of all my records, is there a way to call up a dialog box, input some of the info, and have it display all fields with matching criteria? Thanks again.

 

[CosmoKramer]

Create a query pulling in all of the fields you want to return. In the criteria box for the field you want to search on put something like this:

Like [Enter search criteria] & "*"

This will return all records that start with the criteria entered....

HTH

 

[breaker11]

Cosmo,
Thanks for the tip, it seems like it should work, but I keep getting and error saying, "Type Mismatch." I am not sure what I am doing wrong, I entered everything you suggested. Your help is greatly appreciated.

 

[JoeMiller]

What data type is the field you're searching on? The field type you are searching for is probably a number or a date/time field. The Like operator does not work with these datatypes. What you can do is convert the value of the fields to a string so that the like operator works. So say Field1 is the field we're searching on, change the expression in the field of the query to this:

strField1: CStr([Field1])

And then you're criteria search should work. One other thing, you MAY want to change your parameter to this:

Like "*" & [Enter Search Criteria:] & "*"

Then whatever you enter can be matched in the whole field at any point, Cosmo's way has the query returning any value where the beginning letters/numbers match what the user types in.

HTH Joe Miller
joe.miller@flotech.net

 

[breaker11]

Joe,
I am trying to build a search query to search a number field, and when I entered: strField1: CStr([Field1]) as the field name within the query, it still returned "Type mismatch." Without taking too much of your time, please let me know if you have any more ideas that could help. Thanks again for all of your help.

 

[JoeMiller]

It works in my testing, please post the SQL code in your query by going to View-->SQL View in design mode of the query, Copy/Paste that code to this forum.

Joe Miller
joe.miller@flotech.net

 

[breaker11]

Joe,
I have a main menu list which displays all records. I created a command button that I want to perform this search, here is the sql code for the button:

"SELECT DISTINCTROW Table1.Record, Table1.Product, Table1.ProjectName, CStr([ProductBulkNumber]) AS strProductBulkNumber, Table1.ProjectNumber, Table1.FormulaNumber, Table1.ProductCategory FROM Table1 WHERE (((CStr([ProductBulkNumber])) Like " * " & [Enter Product Bulk Number:] & " * &quot);"

Thanks again.

 

[JoeMiller]

I tested this again, with and without using cstr, and it works on a number field regardless whether you convert to a string or not. I can send you a sample db so you can see it if you like, but here's the SQL Code that access generated WITHOUT the conversion:

SELECT DISTINCTROW tblPartPrices.PartPriceID
FROM tblPartPrices
WHERE (((tblPartPrices.PartPriceID) Like "*" & [Enter Number] & "*&quot);

And here it is WITH the conversion.

SELECT DISTINCTROW tblPartPrices.PartPriceID
FROM tblPartPrices
WHERE (((tblPartPrices.PartPriceID) Like "*" & CStr([Enter Number]) & "*&quot);

Try taking out all the fields and try to get it to work with ProductBulkNumber ONLY. Then start adding fields and see if it blows up. If after all this it still won't work, you can either forward me a copy for a look-see or I can send you my sample db.

HTH Joe Miller
joe.miller@flotech.net

 

[breaker11]

Joe,
I just don't think I have the magic touch! I tried taking out all fields, tried with and without the string, and still all I have is my nice little error message to work with. I don't want to keep burdening you with this problem, so let me know if you have time to look at my db, and if you want me to send it to you. Thanks again, your help is greatly appreciated.

Brian

 

[JoeMiller]

Forward away Joe Miller
joe.miller@flotech.net