How To Filter a Report from a Pop-Up VBA question

[moepower]

I followed the MS Knowledge Base Article ID: Q208529 to create a pop-up form to filter a report. I need my pop-up window to have seven fields, four for text strings and three for date fields. My questions are:

1. How do I tell Access that a particular field is either
a date field or string when the strSQL is being put
together?
2. What is the 5 in the statement below mean?
strSQL = Left(strSQL, (Len(strSQL) - 5))

I'm including the relevant Event Procedure script below. thanks,

-----------------------------------
Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 7
If Me(&quot;Filter&quot; &amp; intCounter) <> &quot;&quot; Then
'strSQL = strSQL &amp; &quot;[&quot; &amp; Me(&quot;Filter&quot; &amp; _
intCounter).Tag &amp; &quot;] &quot; _
'&amp; &quot; = &quot; &amp; Chr(34) &amp; Me(&quot;Filter&quot; &amp; _
intCounter) &amp; Chr(34) &amp; &quot;&quot; _
'And &quot;&quot;

strSQL = strSQL &amp; &quot;[&quot; &amp; Me(&quot;Filter&quot; &amp; _
intCounter).Tag &amp; &quot;] &quot; _
&amp; &quot; = &quot; &amp; Chr(34) &amp; Me(&quot;Filter&quot; &amp; intCounter) _
&amp; Chr(34) &amp; &quot; And &quot;
End If
Next

If strSQL <> &quot;&quot; Then
' Strip Last &quot; And &quot;
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptCustomers].Filter = strSQL
Reports![rptCustomers].FilterOn = True
End If
End Sub
--------------------------------------------

 

[sko]

1. In the example from the Knowledge Base, all the fields are text. That's why the double quotes Chr(34) surround the control name. The code is looping through all the fields (intCounter 1 to 7) and adding the quotes. If the field holds a date, it has to be surrounded by #. Therefore, the code example is not going to work in your case. There is a good example of a criteria form in BEGINNING ACCESS 2000 VBA published by Wrox.

2. The strSQL you are building reads something like this:
CompanyName = user's selection in the first field AND CompanyContact = user's selection in the second field AND City = user's selection in the third field AND


The AND is used to connect each section but the last AND is not needed. That's why it's stripped.

If strSQL <> &quot;&quot; Then
Means if the user selected any criteria then
strip the last AND

strSQL = Left(strSQL, (Len(strSQL) - 5))

strSQL = Starting at the leftmost character in the string, take the total length of the string except for the last five charaters which would be the last AND including spaces

HTH