Create multi criteria query

N

NetworkTrade

Access2003. Couldn't decide whether this question should go into
programming, queries, or reports...

Ultimately am creating a reports and labels based on a query for which the
criteria is dynamically entered by the user using a Form.

Its a general question "how would you do it".

The single table is Contact info. The multiple criteria that the user would
enter is:

Rating: there are about 7 values (A+ thru F) and they want to enter single,
multiple and have an 'all'

Date: will want to do a range i.e. between 1/1/08 and 1/31/08 (again would
need to have an 'all')

Region: there about 5 values and they would want to enter single, multiple
and have an 'all'

Build the query with VBA or in Report's Opening Argument?

One benefit of VBA would be that they could be written once and reused
whether opening a List Report or Label Report

Not real clear on best way for them to enter multiple values for Rating or
Region that will not be user error prone.

Kind of tossing it around and would welcome input if you have done this a
few times maybe a link to a sample of code if possible....thanks in advance...
 
K

Klatuu

If you are familiar with how to add All to a combo box list, then this
concept will work for you.

The example below is actually used to fiter a form, but the concept will
work for a report as well. Basically, you build a filter string based on the
values in the controls you use for filtering. Then use the string you have
built in the Where argument of the report.

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

*****************************************
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities"
GoTo AddAnd_Exit

End Function
 
N

NetworkTrade

thank you for your reply. I used your code as an outline for what I have done.

--
NTC


Klatuu said:
If you are familiar with how to add All to a combo box list, then this
concept will work for you.

The example below is actually used to fiter a form, but the concept will
work for a report as well. Basically, you build a filter string based on the
values in the controls you use for filtering. Then use the string you have
built in the Where argument of the report.

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

*****************************************
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities"
GoTo AddAnd_Exit

End Function


--
Dave Hargis, Microsoft Access MVP


NetworkTrade said:
Access2003. Couldn't decide whether this question should go into
programming, queries, or reports...

Ultimately am creating a reports and labels based on a query for which the
criteria is dynamically entered by the user using a Form.

Its a general question "how would you do it".

The single table is Contact info. The multiple criteria that the user would
enter is:

Rating: there are about 7 values (A+ thru F) and they want to enter single,
multiple and have an 'all'

Date: will want to do a range i.e. between 1/1/08 and 1/31/08 (again would
need to have an 'all')

Region: there about 5 values and they would want to enter single, multiple
and have an 'all'

Build the query with VBA or in Report's Opening Argument?

One benefit of VBA would be that they could be written once and reused
whether opening a List Report or Label Report

Not real clear on best way for them to enter multiple values for Rating or
Region that will not be user error prone.

Kind of tossing it around and would welcome input if you have done this a
few times maybe a link to a sample of code if possible....thanks in advance...
Click to expand...
Click to expand...
 

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

Page numbering with Merged Document and IF statements 1
Query Criteria 3
Multiple parameter query - need Help!!! 5
show criteria data from a query on a report using that query 2
Dynamic search criteria from an array? 1
Multi based criteria 1
Keyword search across multiple tables 11
VBA Macro Training in Chennai 0

Top