Allen, you are the best!!! It worked! Thanks for making my day!
:
The 2 halves go together something like this:
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptEmployeeData"
strField = "dateactive"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.List28
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, ,strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Allen, I don't seem to know what I'm doing here, can you please take
a
look
at the mess I have and help me figure this out. Thanks!
Private Sub ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptEmployeeData"
strField = "dateactive"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " &
Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
DoCmd.Requery
' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere1
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list
box.
'Author: Allen J Browne, 2004.
http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
strWhere3 = strWhere & " AND " & strWhere2
'strDelim = """" 'Delimiter appropriate to field type.
See
note 1.
strDoc = "rptEmployeeData"
'Loop through the ItemsSelected in the list box.
With Me.List28
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
'Report will not filter if open, so close it. For Access 97, see
note
3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere3,
OpenArgs:=strDescrip
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
:
Which line gives that error?
And what is the value of the string when the error occurs?
You can discover that by using:
Debug.Print strWhere
or whatever th string is called, and then looking in the Immediate
Window
(Ctrl+G.)
Thanks for responding Allen!...Although now I keep getting this
error
message
"Error 3075, syntax Error (missing operator) in query expression
'(And)'." -
any recomendation on overcoming that error?
:
To see how to build the filter string for the date, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
To see how to build the filter string for the multi-select list
box,
see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
After building both strings, concatenate them together, e.g.:
strWhere3 = strWhere1 & " AND " & strWhere2
You can then use strWhere3 as the Filter for a form, or as the
WhereCondition for OpenReport.
I'm hoping that someone can help me out....I am trying to
create a
Dialog
form that allows the user to select (filter) the following
different
types
of
ways that a report can access information. I have a startdate
field,
an
enddate field and a listbox in a form.
I would like the user to be able to do the following:
1. select a date range or just the ending date and no items in
the
list
box
(output is all items within the date range).
2. leave the date range blank and just select an item in the
list
box
(output is all date ranges for the select list item.
3. Select mutiple list items (with of without a date range).
4. Leave everything blank (to include all data).
Any help is greatly appreciated....and would certianly make my
day!
I
really hope that someone thinks that this is really doable.
