CODEWITHSUNDEEP
sqlms-accessvbscriptvba
user3046739
user3046739

Reputation: 133

What is the best approach to Query one Table with a combo box in MS Access containing multiple search options?

I'm new to this so don't be to hard on me. What i'm doing is writing an IF Else statement so for Ex. 

    If Combo36.Value = "Contains" Then
            DoCmd.OpenForm "Test", , "", "[LastName] LIKE" + Contains + "", acNormal
    ElseIf Combo36.Value = "Is" Then
            DoCmd.OpenForm "Test", , "", "[LastName] =" + Matches + "", acNormal
    ElseIf Combo36.Value = "Begins With" Then
            DoCmd.OpenForm "Test", , "", "[LastName] LIKE" + BeginsWith + "", acNormal
    ElseIf Combo36.Value = "Ends With" Then
            DoCmd.OpenForm "Test", , "", "[LastName] LIKE" + EndsWith + "", acNormal
    ElseIf Combo36.Value = "Is Not" Then
            DoCmd.OpenForm "Test", , "", "[LastName] <>" + Matches + "", acNormal

I have combo box and textbox for fname, lname, and code. What is the best approach for something like this? How can I prevent it from returning data if both criterias arent met(Search John Evans) In database John is there but not John Evans. I know I can write a bunch of IF statements like 

    "DoCmd.OpenForm "Test", , "", "([FirstName] LIKE" + Contains1 + "And" + "[LastName] Like" + Contains + ")" + "", acNormal",

but like I said I'm new to this so is there a more efficient approach?

Upvotes: 1

Views: 77

Answers (1)

user3046739
user3046739

Reputation: 133

Okay I figured out the best approach opposed to writing a lot of if statements. I created three functions for each value field. Inside the functions I have

1st Function

Function valueInputFN(value As String) As String
If Forms![Searches]![Combo38] = "Is" Then
valueInputFN = ""

ElseIf Forms![Searches]![Combo38] = "Begins With" Then

ElseIf Forms![Searches]![Combo38] = "Contains" Then
valueInputFN = "*"

ElseIf Forms![Searches]![Combo38] = "Ends With" Then
valueInputFN = "*"
End If


End Function

 2nd Function
Function FirstNameFunc(Name1 As String) As String

If IsNull(Forms![Searches]![Text40].value) Then
 FirstNameFunc = "*"
 ElseIf Forms![Searches]![Text40].value = "" Then
 FirstNameFunc = "*"
Else
Forms![Searches]![Text40].SetFocus
 FirstNameFunc = Forms![Searches]![Text40].Text

 End If



End Function
3rd Function 
Function valueInput1FN(value As String) As String
If Forms![Searches]![Combo38] = "Is" Then
valueInput1FN = ""
End If
If Forms![Searches]![Combo38] = "Ends With" Then
valueInput1FN = ""
End If
If Forms![Searches]![Combo38] = "Contains" Then
valueInput1FN = "*"
End If
If Forms![Searches]![Combo38] = "Begins With" Then
valueInput1FN = "*"
End If    

End Function

Above is the three functions and below is what I have in the query critera.

Like valueInputFN("«value»") & FirstNameFunc("«Name1»") & valueInput1FN("«value»") Or "" Or Is Null

This works Like a charm! Now user can select Is or Begins With or Contains or Ends with and the correct data will be retrieved. I'm sure there may have been a better approach but this worked perfect for me.

Upvotes: 1

Related Questions