Reporting on a value range

[sexton75 via AccessMonster.com]

I have a reporting page with various unbound combo boxes. I have
successfully used two of these to populate criteria for a date range.
Ideally, I would like to add two more boxes to allow the user to input
minimum and maximum production ranges in addition to the dates, but I cannot
get the code to work.

The original code for the date ranges is listed below and works great:

Private Sub rpt1_Click()

strWhere = "True"

If Not IsNull(Me.RptCat1) Then
strWhere = strWhere & " And [ckdtin] >=#" & _
Me.RptCat1 & "# "
End If

If Not IsNull(Me.RptCat2) Then
strWhere = strWhere & " And [ckdtin] <=#" & _
Me.RptCat2 & "# "
End If

DoCmd.OpenReport Me![cboReport], acViewPreview, , strWhere

End Sub

When I added the last two parts, it will not pull the quantity in the ranges
selected. The quantity field is just a number and includes the portions with
RptCat3 & RptCat4. The entire code is listed below:

Private Sub rpt1_Click()

strWhere = "True"

If Not IsNull(Me.RptCat1) Then
strWhere = strWhere & " And [ckdtin] >=#" & _
Me.RptCat1 & "# "
End If

If Not IsNull(Me.RptCat2) Then
strWhere = strWhere & " And [ckdtin] <=#" & _
Me.RptCat2 & "# "
End If

If Not IsNull(Me.RptCat3) Then
strWhere = strWhere & " And [Quantity] >=#" & _
Me.RptCat3 & "# "
End If

If Not IsNull(Me.RptCat4) Then
strWhere = strWhere & " And [Quantity] <=#" & _
Me.RptCat4 & "# "
End If

DoCmd.OpenReport Me![cboReport], acViewPreview, , strWhere

End Sub

Please help!

 

[John Spencer]

Dates are delimited with "#" signs, text with quote marks, and numbers are not
delimited (surrounded by specific characters)

Private Sub rpt1_Click()

strWhere = "True"

If Not IsNull(Me.RptCat1) Then
strWhere = strWhere & " And [ckdtin] >=#" & _
Me.RptCat1 & "# "
End If

If Not IsNull(Me.RptCat2) Then
strWhere = strWhere & " And [ckdtin] <=#" & _
Me.RptCat2 & "# "
End If

If Not IsNull(Me.RptCat3) Then
strWhere = strWhere & " And [Quantity] >=" & _
Me.RptCat3
End If

If Not IsNull(Me.RptCat4) Then
strWhere = strWhere & " And [Quantity] <=" & _
Me.RptCat4
End If

DoCmd.OpenReport Me![cboReport], acViewPreview, , strWhere

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Duane Hookom]

Nice code. Since Quantity is a numeric field you need to remove the # (date
delimiters):
If Not IsNull(Me.RptCat3) Then
strWhere = strWhere & " And [Quantity] >=" & _
Me.RptCat3 & " "
End If

If Not IsNull(Me.RptCat4) Then
strWhere = strWhere & " And [Quantity] <=" & _
Me.RptCat4 & " "
End If