Search Criteria

[Clayhead22]

Hi Please Help

I have a multiple item form with search boxes. I want to be able to type in one of the boxes OR 2,3,4 of the boxes. click search and the form will filter based on the criteria.

This is what i have so far.

Private Sub Search_Accounts_Click()
Me.Filter = "Account_ID= " & Me.Search_Account_ID & " AND First_Name= " & Me.Search_First_Name & " AND Last_Name= " & Me.Search_Last_Name & " AND Telephone= " & Me.Search_Telephone & " AND Mobile= " & Me.Search_Mobile & " AND Email= " & Me.Search_Email & " AND Date_of_Birth= " & Me.Search_Date_of_Birth
Me.FilterOn = True
End Sub

 

[plog]

You should then compose your filter screen with a bunch of If statements, not all at once. You would test each input box for data, if it has it, you would use the data. If not you would not use it.

In general this is what it would look like:

Private Sub Search_Accounts_Click()
str_Filter = "(1=1)"
  ' filter string, will be dynamically built based on what inputs were used

If IsNull(Input1)=False Then str_Filter = str_Filter & " AND ([Field1]='" & Input1 & "')"
If IsNull(Input2)=False Then str_Filter = str_Filter & " AND ([Field2]='" & Input2 & "')"
If IsNull(Input3)=False Then str_Filter = str_Filter & " AND ([Field3]='" & Input3 & "')"
... and so on for each input
    
Me.Filter=str_Filter
Me.FilterOn = True
End Sub

 

[Clayhead22]

Thanks. I have created the below however i get a data mis-match error. Im still learning VBA can you spot what i am doing wrong?

Private Sub Search_Accounts_Click()
str_Filter = "(1=1)"
  ' filter string, will be dynamically built based on what inputs were used

If IsNull(Search_Account_ID) = False Then str_Filter = str_Filter & " AND ([Account_ID]='" & Search_Account_ID & "')"
If IsNull(Search_First_Name) = False Then str_Filter = str_Filter & " AND ([First_Name]='" & Search_First_Name & "')"
If IsNull(Search_Last_Name) = False Then str_Filter = str_Filter & " AND ([Last_Name]='" & Search_Last_Name & "')"
If IsNull(Search_Telephone) = False Then str_Filter = str_Filter & " AND ([Telephone]='" & Search_Telephone & "')"
If IsNull(Search_Mobile) = False Then str_Filter = str_Filter & " AND ([Mobile]='" & Search_Mobile & "')"
If IsNull(Search_Email) = False Then str_Filter = str_Filter & " AND ([Email]='" & Search_Email & "')"
If IsNull(Search_Date_of_Birth) = False Then str_Filter = str_Filter & " AND ([Date_of_Birth]='" & Search_Date_of_Birth & "')"

Me.Filter = str_Filter
Me.FilterOn = True


End Sub

 

[plog]

In all your IsNull() functions, I'm pretty sure that's not how you reference a form input.

Also, divide and conquer. Do you know which line is not working? If not, eliminate all the lines but one and get that one line to work.

 

[Clayhead22]

Sorry yes i changes to

If IsNull(Me.Search_Account_ID) = False Then str_Filter = str_Filter & " AND ([Account_ID]='" & Me.Search_Account_ID & "')"
If IsNull(Me.Search_First_Name) = False Then str_Filter = str_Filter & " AND ([First_Name]='" & Me.Search_First_Name & "')"
If IsNull(Me.Search_Last_Name) = False Then str_Filter = str_Filter & " AND ([Last_Name]='" & Me.Search_Last_Name & "')"
If IsNull(Me.Search_Telephone) = False Then str_Filter = str_Filter & " AND ([Telephone]='" & Me.Search_Telephone & "')"
If IsNull(Me.Search_Mobile) = False Then str_Filter = str_Filter & " AND ([Mobile]='" & Me.Search_Mobile & "')"
If IsNull(Me.Search_Email) = False Then str_Filter = str_Filter & " AND ([Email]='" & Me.Search_Email & "')"
If IsNull(Me.Search_Date_of_Birth) = False Then str_Filter = str_Filter & " AND ([Date_of_Birth]='" & Me.Search_Date_of_Birth & "')"

However its this line that debug does not like.

Me.Filter = str_Filter

 

[plog]

Comment out all those if statements and see if it works when str_Filter="(1=1)"

 

[Clayhead22]

ok that does not bring back any error without the IFs

Added back in the IFs and if i do not fill in a criteria box then it does not error either however when i do it gives the same error

 

[plog]

Now, uncomment just the first If statement. Divide and conquer.

 

[Clayhead22]

Ahh i see what you mean. I have done all individually and the only ones that do not work are account Id and Date of birth

I have tried a few things now and do not seem to be able to get these two to work..

 

[plog]

Id is most likely a number, which means you can remove the single quotes before and after it. For the date field you need to use # before and after it. Forget if you need single quotes or not.

 

[Clayhead22]

Thank you i have the number working

One last thing, i dont seem to see where i need to put the # # Apologies i am still very new to all of this and maybe punching above my weight with the project i am working on.

If IsNull(#Me.Search_Date_of_Birth.Value#) = False Then str_Filter = str_Filter & " AND ([Date_of_Birth]='" & #Me.Search_Date_of_Birth# & "')"

 

[plog]

I hate dates. I don't know if it is this:

"[Date_of_Birth]=#'" & Me.Search_Date_of_Birth & "#'"

Or This:

"[Date_of_Birth]=#" & Me.Search_Date_of_Birth & "#"

It's hard to see, but the top has single quotes and the bottom one doesn't. it's one of them.

 

[Clayhead22]

Got It

If IsNull(Me.Search_Date_of_Birth.Value) = False Then str_Filter = str_Filter & " AND ([Date_of_Birth]=#" & Me.Search_Date_of_Birth & "#)"

plog you are an absolute legend. Thank you for your help!