Date Range & Multi-Select List Box


G

Guest

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.
 
Ad

Advertisements

A

Allen Browne

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.
 
G

Guest

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?
 
A

Allen Browne

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.)
 
G

Guest

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
 
A

Allen Browne

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.

Stacey said:
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


Allen Browne said:
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.)
 
Ad

Advertisements

G

Guest

Allen, you are the best!!! It worked! Thanks for making my day!

Allen Browne said:
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.

Stacey said:
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


Allen Browne said:
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.
 
G

Guest

Allen, I know this is a stupid question...but how do you go about adding one
more combo box?

Stacey said:
Allen, you are the best!!! It worked! Thanks for making my day!

Allen Browne said:
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.

Stacey said:
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.
 
A

Allen Browne

Stacey, download this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The example database is set up so you can easily add more criteria, and
covers:
- combos
- date fields
- ranges
- exact matches
- partial matches
- text fields
- numeric fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Stacey said:
Allen, I know this is a stupid question...but how do you go about adding
one
more combo box?

Stacey said:
Allen, you are the best!!! It worked! Thanks for making my day!

Allen Browne said:
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.
 
G

Guest

Thanks once again Allen...I really do appreciate your help with this.

Allen Browne said:
Stacey, download this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The example database is set up so you can easily add more criteria, and
covers:
- combos
- date fields
- ranges
- exact matches
- partial matches
- text fields
- numeric fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Stacey said:
Allen, I know this is a stupid question...but how do you go about adding
one
more combo box?

Stacey said:
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.
 
Ad

Advertisements

Joined
May 5, 2015
Messages
1
Reaction score
0
I hope that someone still looks at this post.
I'm using the code that Alan proposed.
I'm having 2 listbox that I want to use, but I don't know how to adapt the code to build the string.
The filter works fine with listbox lstAuthor, but when I try to ad the where condition for lstPublicationType is bugs.
I know that I have to put the strWhere4 somewhere, but I don't know how to.

Can anyone help me out?

Private Sub cmdOpenReport_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 strWhere4 As String

Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptPublications"
strField = "PublicationDate"
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.lstAuthor
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 = "[WritersID] IN (" & Left$(strWhere2, lngLen) & ")"
End If
With Me.lstPublication_type
For Each varItm In .ItemsSelected
If Not IsNull(varItm) Then
'Build up the filter from the bound column (hidden).
strWhere4 = strWhere4 & strDelim & .ItemData(varItm) & strDelim & ","
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere4) - 1
If lngLen > 0 Then
strWhere4 = "[PublicationTypeID] IN (" & Left$(strThere, 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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Multi Select List boxes 10
Multi Select in a list box 1
list box -- Multi Select 4
Multi-select List Boxes 5
Multi Select List BoX 0
Multi-Select List Box (?) 1
Multi Select List Box 4
Multi-Select List Box 3

Top