Please help with VBA filter (1 Viewer)

[Clayhead22]

Hi All. I am still trying to wrap my head around some of the simple code. Please can someone help with the below.

I have created a button called "Search". When clicking it should look at the data put into 1 or more of 7 text boxes (with search criteria typed in) and filter a continuous form based. The code i have currently is below but it requires all text boxes to be complete to return the records. I want to be able to search via any combination of criteria's

Me.Filter = "[Account ID] = '" & Me.SearchAccountID & "'"
Me.Filter = "[Account Name] = '" & Me.SearchAccountName & "'"
Me.Filter = "[Type] = '" & Me.SearchAccountType & "'"
Me.Filter = "[Postal Code] = '" & Me.SearchAccountPostCode & "'"
Me.Filter = "[Telephone] = '" & Me.SearchAccountTelephone & "'"
Me.Filter = "[Mobile] = '" & Me.SearchAccountMobile & "'"
Me.Filter = "[Mail] = '" & Me.SearchAccountMail & "'"
Me.FilterOn = True

 

[pbaldy]

That works? I'd expect it to only filter on Mail, as you would be overwriting the filter each time. I'd use dynamic SQL, as shown in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

You can do something similar to conditionally build a filter, but each field would be separated by "AND".