Search function in acces

[stevie23]

hi All,



I hope that someone can help me. I have a database in acces and also a Form. This consists of different part numbers on different identifcation level.

1 part can have small different options and there the second level is c level which all go under the same D (level 1).

Now i want to have a search funcion(query or form)build that i can just search 123456789 and that all forms which have this information in (in all columns) show in the results.

ID	DPART_PART_NO	CPART_PART_NO	DESCRIPTION	FIN_SUB_BUSINESS_CODE	Field1	Field2
1	123456789 D	123456789 C	B & P	MEX	0	0
2	123456789 D	987654321 C	B & P	MEX	0	0

[CJ_London]

you need to build a filter to compare all fields with your text entry - perhaps in the click event to a button

me.filter="dpart_part_no like '*" & me.txtsearch & "*' OR cpart_part_no like '*" & me.txtsearch & "*' OR ........
me.filteron=true


the above assumes you are entering a partial entry in your search field which could be anywhere in the field value and is the slowest method because indexing cannot be used. If you are entering a full entry then you can use

me.filter="dpart_part_no ='" & me.txtsearch & "' OR cpart_part_no = '" & me.txtsearch & "' OR ........
me.filteron=true

which will use indexing

or if users will know the beginning then this will be as fast as the indexed search

me.filter="dpart_part_no like '" & me.txtsearch & "*' OR cpart_part_no like '" & me.txtsearch & "*' OR ........
me.filteron=true

[stevie23]

is there somewhere like a guide where i can follow to build this filter?Thanks

[CJ_London]

your requirement is a common one so can't suggest a guide as such - only other threads suggesting the same as I have in my post - not quite sure what you don't understand

Look to the bottom of this thread where others have asked the same question or google something like 'access vba create search form' or similar to find youtube videos and threads/suggestions on other sites

[stevie23]

thanks again for your help!

[orange]

Stevie,

See if this search can be applied to your situation.

[CJ_London]

if you are trying to understand about and's and or's, think of them a bit like addition (for and's ) and multiplication (for or's) in a formula. All evaluations will evaluate to true or false i.e. dpart_part_no like '" & me.txtsearch & "*' will either be true or false, it can't be anything else. so sometimes you need to use brackets to get the required result

so with a formula

1+(3*5)=16

(1+3)*5=20

true can also be represented a x=1 and false as x=0

so put that into your formula

1=1 and (3=3 or 5=5) evaluates to true

1=0 and (3=0 or 5=5) evaluates to false

1=1 and (3=0 or 5=5) evaluates to true

1=1 and (3=0 or 5=0) evaluates to false

in your case your filter can be represented as

(1=1) or (3=3) or (5=5) - if any evaluation returns true, the whole evaluation will be true

if you used and's instead

(1=1) and (3=3) and (5=5) - if any evaluation returns false, the whole evaluation will be false


Not sure if this clarifies the theory for you