Problem defining multiple criteria

[DubboPete]

Hi all,

I have a report dialog where users can select a date range, then choose
from six different categories in an Option Group.

Example, date range 1-May-05 to 20-Apr-06 and "Leadership & Management"
chosen from Option Group [frame9].

I thought this would filter out just the four (of 12) records where the
Leadership and Management field entry was Yes (yes/no field). It's
pulling 10 of 12 records!

Can anyone help me correct this code so it might work?


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 Len(strField) > 0 Then
strWhere = strWhere & " AND " & BuildCriteria(strField,
dbBoolean, True)
End If

DoCmd.OpenReport "RptEquipSum1", acViewPreview, , strWhere


any help much appreciated

DubboPete

 

[Guest]

Just a thought but have you checked that each option button actually has the
value you've specified in each of their properties? I often cock-up on this
when I copy an option button because it copies its value too.

 

[Baz]

Hi all,

I have a report dialog where users can select a date range, then choose
from six different categories in an Option Group.

Example, date range 1-May-05 to 20-Apr-06 and "Leadership & Management"
chosen from Option Group [frame9].

I thought this would filter out just the four (of 12) records where the
Leadership and Management field entry was Yes (yes/no field). It's
pulling 10 of 12 records!

Can anyone help me correct this code so it might work?


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 Len(strField) > 0 Then
strWhere = strWhere & " AND " & BuildCriteria(strField,
dbBoolean, True)
End If

DoCmd.OpenReport "RptEquipSum1", acViewPreview, , strWhere


any help much appreciated

DubboPete

If part of your criteria is a date range, where does this long-valued
"FacilityID" come into it, and what is in Combo38?

n.b. it really is a good idea to give descriptive names to your controls.
You may know what Command21 and Frame9 and Combo38 are, but no-one else
does, and in a year's time you will not be able to remember either!