Run query twice on wildcard

[bg18461]

I am new to access and have a question on how/if this can be done.

I want to select companies from a large table >50,000 entries.

The user will enter in a name, say 7-eleven. This may be entered in the table with different variations say 7eleven, 7 eleven, 7-eleven, etc...

I would like for the user to enter for example, "eleven". Access would then have a popup box showing all variations in the list on that wildcard search. The user will then select (multiple select if the user wants to return multiple variations on that name) and the query will now return all enteries matching the name(s) the user selected. Any idea on how to set this up?

 

[rockyjr]

In your SQL statement you would want to enter something like this after the WHERE clause:

WHERE (isnull(forms![FormName]!TextBox) Or TableName.FieldName  Like "*" & forms![FormName]!TextBox & "*")

The isnull assures that if the field is blank, it will return everything.

If you have more than one field... you would just add "AND".....

WHERE (isnull(forms![FormName]!TextBox) Or TableName.FieldName  Like "*" & forms![FormName]!TextBox & "*") 
AND (isnull(forms![FormName]!TextBox2) Or TableName.FieldName2  Like "*" & forms![FormName]!TextBox2 & "*")

Hope this helps!!

 

[bg18461]

Thanks Rocky, I think I am doing something wrong. Here is my setup:

Form where user enters customer name:

Form Name: Cus_Input
Textbox Name: Cust_Name
Output variations of customer name into listbox name: Cust_Out
Table Name: TableCustomer
Customer Name Field in Table: CustomerName

I have a command button on the form called "cmdSearch" and in the code I have under that command buttons command, DoCmd.OpenQuery "Query1"

Query1 is the name of the query i am using.

In the query1 I have used your statement but I keep getting an error, this is what I put in the Criteria box under the CustomerName field:

WHERE (isnull(forms![Cus_Input]!Cust_Name) Or TableCustomer.CustomerName Like "*" & forms![Cus_Input]!Cust_Name & "*")

I keep getting the error "Undefined function "WHERE" in expression.

What am i doing wrong, i just want whatever customer name you enter displays all matching variations in the listbox on the form.

 

[Mike375]

When you put it on the Criteria line don't inlude Where. The Where is done in SQL view.

For example I have in a query criteria Is Not Null for the CLDOB field in a table called MainTable.

However, in SQL view it shows as

WHERE (((MainTable.CLDOB) Is Not Null));

 

[bg18461]

Awesome Mike thanks for your reply.

The next step is before it actually runs the query, I would like it to be a 2 step process. I would like all variations of the name to be shown in a listbox on the form. The user will next select all variations to by clicking on each one, if multiple, press ctrl and select all variations. Once this is done, it will run the actual query that shows all the data for all the customers names selected in the previous step, any ideas?

 

[Mike375]

Quite honestly you can fiddle about with this sort of thing for a long time and some of that fiddling might be creating a field in a query to get a common entry from the variations of 7-eleven. How that type of thing is best done depends on how many variations you have.

But basically if you want a query to retrieve all the records where the entry is similar than you have that combo data as the criteria in the query with the Like type of entry. The you might have a second query that does not use things such as Like * etc but just has

[froms]![FormName]![ComboName]


In that case it will only find entries that exactly match what was selected in the combo.

You would then probably include in the AfterUpdate event on the combo macro or code that opens the relevant query or form.

 

[bg18461]

Can you give me more guided steps on this Mike or anyone, I am new to all of this...

 

[Mike375]

Like "" & [forms]![YourForm]![TextABC] & "*"

That finds all records based on the first letters entered. For example if Ch was in TextABC it will get Chris, Christopher, Chime and chick etc.

Like "*" & [forms]![YourForm]![TextABC] & "*"

That finds where Ch occurs so it would get Chris, Nicolas and Richard

[forms]![YourForm]![TextABC] gets on the exact entry and so Chris will only get the records with Chris and not Christopher.

>=[Forms]![YourForm]![TextABC] And <=[Forms]![YourForm]![TextABC] That one is obvious.

To get your varaition of entries such as 7 Eleven, Seven Eleven as a common entry there are different ways.

The IIF() and Switch() are done in a query and create a new field but they are limited to 7 variations. IIF is dones as Nested IIF which is combining IIFs

You can also do in VBA

If "whatever" Then

"Do this"

End If

The "whatevers" would be each variation and the "do this" would be placing the value you want in the field required. Thus the "whatevers" will have 7 Eleven, Seven Eleven, Seven 11 etc and the "do this" will all be 711 or whatever you chooses. You can do as many as you like. The limitation is you can't make a query field with it. It is done on a form.

The same thing can be done with the macro SetValue action and using the condition column.

You can make a Select Case function in a module and then call the function in a new query field.

The other type of approcah is to make a table that holds the values. For example, you might make a copy of your table that has the Seven 11 type entries and append the original table to the copy. However, in the copy of the table you have the field that receives the Seven 11 type entries set so it won't take duplicates. You only really need to fields in the new table, one for the Seven 11 entries and the other to enter what you want to call them. One of the things you can then do is make the Seven 11 field a primary key field and then update the main table with the common entry you have used for each of the variations. This type of thing is often done as "one off" for existing data and you then change your DB so as to make sure Seven Eleven, Seven 11 etc can be only entered one way. This could be easily done by using a combo box to make the entry and the combo is based on the table with the entries that required.

As I said in my previous post there can be a bit of fiddling about with this sort of thing so you need to do a few searches.

I suspect your best option is to create the table to produce the list of common entries based on the variations.

 

[bg18461]

Thanks Mike that was really helpful in explaining how the wildcard feature worked. It has given my a far better understanding. I was actually able to use Rockyjr's code and it does what I need.

I need help with another query that only shows each variation once. I have created another userform (frmSelectCustomer). On this userform it has a listbox and 3 command buttons (Select All, Select None, and OK). For example, the user enters "7-eleven", the query as it stands now will return (7) "7-eleven", (4) "7eleven", (8) "seveneleven". I would like the query to just have those 3 variations in it. The user can now select which variation they want to run the full query on by selecting either any of those names, or pressing ctrl+other names for multiple selection, or the select all button. It should now return the values selected back into the original form which also has a listbox. The user now presses the run command button which will now search only those variations exactly how its spelled and return a listing of all the information.

If there is only one variation on the search it should not even show the frmselectcustomer and only put that one variation in the original form for the user to search on it.

If I have not made this clear, please let me know Mike and I will try again to explain what I mean. Thanks for your help thus far, you have been tremendous.

 

[Mike375]

I can't spend much more time on it now. But look up GroupBy for queries. It is acitivated by clicking the E symbol on the Query Design screen toolbar.

 

[rockyjr]

If you are only searching for one field, and only want one name returned and not the same multiple times... you can try this:

Add the word "distinct" to the SQL statement:
ex: Select distinct.....