Searching

[Disturbedguy]

Hey all.

I have a database but want to create a Search function.

I want the search function to read a drop down box for a table name, then read two text box's for search criteria then search the table and displat results.

My knowledge of Microsoft Access is limited.

I think I have the theory behind it but not the know how for the coding.

So far I have been able to get it too look at the List Box for the table name then when I hit the search button ive set it to just open the table for now.

but cant get any further, any help would be greatly appreciated.

Thanks in advance.

 

[Disturbedguy]

WOW I mean people come for help and get no replies how amazing.

Sorry if this is found offensive but Microsoft Access Help Center to me means, if I post something I get help but ahh well maybe not.

 

[Brando]

Try this...

The following example should solve your issues. I am also a novice at this, but someone was generous enough to get me started and this works perfectly. (insert your names where appropriate)
--Brando


Private Sub YourSearchButton_Click()

If Len(YourComboBox) = 0 Or IsNull(YourComboBox) = True Then
MsgBox "You must select a field to search."

ElseIf Len(YourTextBox) = 0 Or IsNull(YourTextBox) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = YourComboBox.Value & " LIKE '*" & YourTextBox & "*'"

'Filter YourFormName based on search criteria
Form_YourFormName.RecordSource = "select * from YourTableName where " & GCriteria
Form_YourFormName.Caption = "YourTableName (" & YourComboBox.Value & " contains '*" & YourTextBox & "*')"

'Close YourSearchForm.
DoCmd.Close acForm, "YourSearchForm"

MsgBox "Search complete."

End If

End Sub

 

[Brianwarnock]

WOW I mean people come for help and get no replies how amazing.

Sorry if this is found offensive but Microsoft Access Help Center to me means, if I post something I get help but ahh well maybe not.

Not responded to Brando yet?

Ahh well some people can be a bit self centred , remind me to put disturbedguy on my ignore list.
Brian

 

[fly_boy]

Hi. I have a similar question, but I would like to have a search with two diferent fields (like for example "Sex" and "Age"). What do I have to change in the code? will it be someting like this:

GCriteria = (YourComboBoxA.Value & " LIKE '*" & YourTextBoxA & "*'") AND (YourComboBoxB.Value & " LIKE '*" & YourTextBoxB & "*'")

 

[EMP]

Age is a number. We seldom use the Like operator on numeric fields.

Take a look at the sample database in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[fly_boy]

ok, "age" was just an example. what if there were no numeric fields? the rest of the code was correct? is tha "And" the correct operator to use?

 

[EMP]

is tha "And" the correct operator to use?

It all depends on what you want the select statement to do.

When AND is used, both conditions must be true in the record. Otherwise the record is not returned.

When OR is used, either one condition needs to be true.

^