CODEWITHSUNDEEP
vbams-access
user12045437
user12045437

Reputation:

Filter by multiple parameters on Access form

I have a MS Access form that I want to filter the database based on a SQL statement.
The form will use multiple parameters, but I want it so that not all fields are required to perform the filter.

An example would be: User wants to query only by Date and Product and leave Customer and Analysis blank.

These are the fields in the form:

Picture of form fields

So far I have tried the following statements and using "LIKE" but it is returning blank results. I have only tried with two fields and it isn't working.

Public Sub Command121_Click()

Dim task As String

task = "select * from SageOrderLines_Live where [PromisedDeliveryDate] = " & Format(Me.DateFrom, "\#dd\/mm\/yyyy\#") & " AND [CustomerAccountNumber] LIKE "" & Me.CustomerAccount & """

DoCmd.ApplyFilter task

End Sub

Upvotes: 0

Views: 1975

Answers (1)

June7
June7

Reputation: 21379

Using LIKE without wildcard might as well be = sign.

Use of quote delimiters is incorrect - really need another quote on each side.

" AND [CustomerAccountNumber] LIKE """ & Me.CustomerAccount & "*"""

Or make it easier to read and use apostrophe instead of doubled quotes.

" AND [CustomerAccountNumber] LIKE '" & Me.CustomerAccount & "*'"

Upvotes: 1

Related Questions