Filter that uses the "Between" function

[Brando]

Thank you in advance for your help.

I have a filter that uses multiple criteria to produce a report. It works great with one exception. When the Between function is followed by another criteria (such as "Action" required) I get an error message: Run-time error '3075': Syntax error (missing operator) in query expression 'SaleDate Between [txtSaleFrom] And [txtSaleTo][Action] =-1'. Any other combination of criteria works great. Am I missing an "AND"?

The first part of the code for the filter is:

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter

' Check for Sale Dates within the range.
If Me.txtSaleFrom > "" Then
varWhere = varWhere & "[SaleDate] Between [txtSaleFrom] And [txtSaleTo]"
End If

' Check for LIKE Product Type
If Me.cboProductType > "" Then
varWhere = varWhere & "[ProductType] LIKE """ & Me.cboProductType & "*"" AND "
End If

' Check for LIKE Product Number
If Me.txtProductNo > "" Then
varWhere = varWhere & "[ProductNo] LIKE """ & Me.txtProductNo & "*"" AND "
End If

' Check for Action Required
If Me.chkAction < 0 Then
varWhere = varWhere & "[Action] = " & Me.chkAction & " AND "
End If

 

[RuralGuy]

Use a MsgBox or Debug.Print to display the string before you use it and you will see the problem.

 

[Brando]

I used "Debug.Print BuildFilter" and the Imediate window says the following:

WHERE [SaleDate] Between [txtSaleFrom] And [txtSaleTo][Action] = -1

It still makes me think I must be missing an "AND" between the ][. However, so far, I have not been able to figure out how to do it.

 

[Brando]

Oops. I see it now. I had a " in the wrong place which was changing the "AND" to an "And". As soon as I removed it, the filter worked perfectly. Thank you.

 

[RuralGuy]

Glad to be of service. Thanks for posting back with your success.