Combo and Select Case filter not working

D

DubboPete

Hi all,

I have a form which is the basis for a report. On the form I have a
list of facilities in Combo38. Then, once a facility has been
selected, there are six choices in a frame to further filter down the
records.

The filter is supposed to find all records where, for instance, the
facility is Drug and Alcohol Unit, and the category is "Continuum Of
Care".

Once selected, I then click a command button to preview the filtered
report. The only problem is, the frame's filter part is not
'filtering' ! The combo box filter seems to be working, but I see all
records for the particular facility, not just the selected choice in
the frame.

Here's the code:

Private Sub Command21_Click()

Dim strField As String
Dim strWhere As String

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)

If strField = "Yes" Then
strWhere = strWhere & " AND " & BuildCriteria(strField,
dbBoolean, True)
End If


DoCmd.OpenReport "RptEquipSum1", acViewPreview, , strWhere

End Sub

Can anybody see any flaws in the code, and if not, why aint it
filtering properly?? :)

DubboPete
 
D

DubboPete

Thanks Steve, made sense out of it now, and got it filtering
correctly...
I created another string StrWhere2, and the resulting code changes at
execution point towards the end.

Here's the corrected code:

Dim strField As String
Dim strWhere As String
Dim strWhere2 As String

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)
strWhere2 = strWhere & " AND " & BuildCriteria(strField, dbBoolean,
True)
DubboPete,

You have a Select Case where you set "[strField ]" = ......... (to
everything except YES), then you check if "[strField]"= "Yes".

Why do you check for "Yes" (If strField = "Yes" Then) when it appears that
"[strField ] " will never be set to "Yes"????


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


DubboPete said:
Hi all,

I have a form which is the basis for a report. On the form I have a
list of facilities in Combo38. Then, once a facility has been
selected, there are six choices in a frame to further filter down the
records.

The filter is supposed to find all records where, for instance, the
facility is Drug and Alcohol Unit, and the category is "Continuum Of
Care".

Once selected, I then click a command button to preview the filtered
report. The only problem is, the frame's filter part is not
'filtering' ! The combo box filter seems to be working, but I see all
records for the particular facility, not just the selected choice in
the frame.

Here's the code:

Private Sub Command21_Click()

Dim strField As String
Dim strWhere As String

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)

If strField = "Yes" Then
strWhere = strWhere & " AND " & BuildCriteria(strField,
dbBoolean, True)
End If


DoCmd.OpenReport "RptEquipSum1", acViewPreview, , strWhere

End Sub

Can anybody see any flaws in the code, and if not, why aint it
filtering properly?? :)

DubboPete
Click to expand...
Click to expand...
 
S

SteveS

Great.

BTW, I don't know what "BuildCriteria( ) " does, but since you don't have
the IF() statement, you could just use

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)
strWhere = strWhere & " AND " & BuildCriteria(strField, dbBoolean,True)

You don't really need to use "strWhere2" since you are concatenating the two
lines.

Also, if you want to save some typing, you don't need to use ".Value"
(as in "Me.Combo38.Value") because Value is the default property.

You can just use "Me.Combo38" .


Good luck
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


DubboPete said:
Thanks Steve, made sense out of it now, and got it filtering
correctly...
I created another string StrWhere2, and the resulting code changes at
execution point towards the end.

Here's the corrected code:

Dim strField As String
Dim strWhere As String
Dim strWhere2 As String

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)
strWhere2 = strWhere & " AND " & BuildCriteria(strField, dbBoolean,
True)
DubboPete,

You have a Select Case where you set "[strField ]" = ......... (to
everything except YES), then you check if "[strField]"= "Yes".

Why do you check for "Yes" (If strField = "Yes" Then) when it appears that
"[strField ] " will never be set to "Yes"????


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


DubboPete said:
Hi all,

I have a form which is the basis for a report. On the form I have a
list of facilities in Combo38. Then, once a facility has been
selected, there are six choices in a frame to further filter down the
records.

The filter is supposed to find all records where, for instance, the
facility is Drug and Alcohol Unit, and the category is "Continuum Of
Care".

Once selected, I then click a command button to preview the filtered
report. The only problem is, the frame's filter part is not
'filtering' ! The combo box filter seems to be working, but I see all
records for the particular facility, not just the selected choice in
the frame.

Here's the code:

Private Sub Command21_Click()

Dim strField As String
Dim strWhere As String

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)

If strField = "Yes" Then
strWhere = strWhere & " AND " & BuildCriteria(strField,
dbBoolean, True)
End If


DoCmd.OpenReport "RptEquipSum1", acViewPreview, , strWhere

End Sub

Can anybody see any flaws in the code, and if not, why aint it
filtering properly?? :)

DubboPete
Click to expand...
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

Problem defining multiple criteria 2
Report Preview and RTF discrepancy 2
Filter on Recordset not working 2
Print Filter Not Working 0
Adding where command or filter into code?? 8
filter report from listbox without having to select any 7
Adding "all" to combo box isn't working 3
Multiple Report Filter using form 1

Top