Search Function using multiple criteria in a form

[joshnathan]

Hello All,
I'm quite new to access and very new to access VBA. I do however have some experience with VBA in excel.

I'm currently trying to build in access a replica of an atrocious search function in excel.

I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).

My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered.
- for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june".
- This is attached to a single col called Product Name.

Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)

Then by Market Context (also a drop down)

Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.

I hope this makes sense.

I'm a little out of my depth. I've built something that does this in excel for small amounts of data but its too slow with 10k and its frankly messy.

Any help would be great.

 

[Roku]

It's difficult to grasp what you are trying to achieve from your description

My first thought is the use of a query on the product name column (LIKE *apple*). Do you need any one of the words to be matched, or must all of them match? I'm thinking of multiple concurrent queries for each word to be matched, but that could get unwieldy (not to mention slow).

Is it acceptable to select all records with the first word, then select the subset with second word and so on? Again, might be slow.

If you post your Excel example, it would help to understand what you want.

 

[redalert]

Not too difficult just time consuming as doing lots of things in Access is.

The interesting bit is where, for example you want to search for any / multiple parts of a search string such as "appletreejune".

Do you mean "apple tree june" as there are parts to this string and not "appletreejune"?

 

[Brianwarnock]

I think that this requires just 1 query and a function.
The function will be passed the field and then use Split to create an array of the data in the textbox on the form.
Then a loop will use instr to test if every or any of the strings are in the field dependent on whether all or any one have to exist.

The function will be like
Myfunction (myfield as string) as string

Myarray = Split(forms!myform!mytextbox,",") assuming strings separated by comma

For I =0 to unbound
If instr(myfield,myarray(I)) = 0 then
Myfunction="false"
Exit function
End if
Next I
Myfunction="true"
End function

The above assumes all strings must be present.

In the query fielda: myfunction(myfield)

The Where clause will be
Where fielda="true"
And mark = forms!formname!combo
And market = forms!formname!combomarket

Note that this was coded on the fly and no syntax is guaranteed, look up the functions etc in help

Brian

 

[redalert]

See attached.

Open the frm_SearchForm .

I suggest you use the search words "Chocolate Dried Meat‏" to see what happens.

Does this demonstrate the part of the search function that you need that involves Product Name?

The code can be cribbed / adapted / enhanced as required.