Question Searching for a value from different lists using an ambiguous title....

[CarysW]

I'm creating a user friendly search function for my DB. I have a form which shows the search results with a 'Search' button which brings up a small Search Box. My Search Box has a Combo box whicvh is populated from a list I have written, it includes Name, Account number, Town, Postcode. Account number, town and postcode all work fine when searching - my problem comes when I try to search for name as in my actual list of accounts there are 2 fields; 'Business Name' and 'Trading As'. I want my users to be able to search for both of these fields under the 'Name' option so as not to complicate things - is this something I can put into the code or do I need to change something on my Search Box or Table or the query that the Search form gets it's information from?

Below is the code that I've used(I found the search box idea as a template on the web and changed it accordingly).

 'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
         'Filter frmSearchA based on search criteria
        Form_frmSearchA.RecordSource = "select * from qrySearchF where " & GCriteria
        Form_frmSearchA.Caption = "qrySearchF (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

 

[namliam]

This is pretty simple, you just need to make an If construction that "If combo = "Name" then"
search in 2 fields
else
search in 1 field
endif

Does that make sense to you?

 

[CarysW]

This is pretty simple, you just need to make an If construction that "If combo = "Name" then"
search in 2 fields
else
search in 1 field
endif

Does that make sense to you?

Hmm...I think so.

Not sure how to write the 'search in 2 fields' bit though....

 

[namliam]

2 field search ... use an OR for that..
Something like so:
If Combo = "Name"
GCriteria = ""
GCriteria = GCriteria & " Field1 LIKE '*" & txtSearchString & "*'"
GCriteria = GCriteria & " OR Field2 LIKE '*" & txtSearchString & "*'"

Then continue as per normal...

 

[CarysW]

2 field search ... use an OR for that..
Something like so:
If Combo = "Name"
GCriteria = ""
GCriteria = GCriteria & " Field1 LIKE '*" & txtSearchString & "*'"
GCriteria = GCriteria & " OR Field2 LIKE '*" & txtSearchString & "*'"

Then continue as per normal...

Hmm..can't get it to work - at which point in the code do I need to add it?

 

[namliam]

Hmm..can't get it to work - at which point in the code do I need to add it?

What do you have that doesnt work?

 

[CarysW]

What do you have that doesnt work?

I have dropped it into the code after:

 'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
         'Filter frmSearchA based on search criteria
        Form_frmSearchA.RecordSource = "select * from qrySearchF where " & GCriteria
        Form_frmSearchA.Caption = "qrySearchF (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

Which is probably completely the wrong thing.

Have changed 'Combo' to cboSearchField and Field 1 to Business Name, Field 2 to Trading As.


When I do the search I receive the Enter parameter Name box, and then receive an error.

How wrong am I?

 

[namliam]

1) Dont drop it after that code but into
You need to change the GCriteria...

If combo = "Name" 
My GCriteria
else
Your GCriteria normal line
End if

Query stuff

Also if your column names have spaces in them (which they SHOULD NOT HAVE) make sure to enclose the column names by []... I.e. [Trading As]

 

[CarysW]

1) Dont drop it after that code but into
You need to change the GCriteria...

If combo = "Name" 
My GCriteria
else
Your GCriteria normal line
End if

Query stuff

Also if your column names have spaces in them (which they SHOULD NOT HAVE) make sure to enclose the column names by []... I.e. [Trading As]

It works!! Thank you, I would add to your rep but it wont let me until i spread it aroumd a bit more!

 

[namliam]

It works!! Thank you, I would add to your rep but it wont let me until i spread it aroumd a bit more!

Guess I should stop helping you

Not fussed about it working, I am more intrested in "do you now see what is going on??" so you can do it yourself next time and/or show someone else how to do something simular!

 

[CarysW]

Guess I should stop helping you

Not fussed about it working, I am more intrested in "do you now see what is going on??" so you can do it yourself next time and/or show someone else how to do something simular!

I think I do, I'm telling it that if the combo is pointing to name then it needs to look in those 2 fields, if not let it do it's own thing.

 

[namliam]

Yes, you can so something simular to allow for wildcard or exact searches to add *x* or only search for x.

Any many more happy applications