report from check boxes

[Guest]

Your not going to believe this... but that was it. It works! Thank you so
much! Is there a easy way to change the filter from displaying 1's to Yes's
or something? I know I've taken a lot of your time already, so if your done
with me let me know. I am very appreciative of all your help!

Hi Jenny

Is SpecialtiesFK a text field or a numeric field?

Is the PK of your Specialties table a text field or a numeric (or
autonumber) field?

[The answer to these should both be the same!!]

If the answer is "numeric" then your combo box properties are wrong.

The RowSource should be:
Select SpecialtyID, SpecialtyName from Specialties order by SpecialtyName;

ColumnCount should be 2
ColumnWidths should be 0 (this hides the first column)
BoundColumn should be 1

This will give you a list of numbers in your subquery - for example:
(CSDID in (Select CSDFK from CSD_Categories where SpecialtiesFK in (1,3,5)))

If the answer is "text" then you have a slightly different design from what
I've suggested, by no matter. Because your "IN" list is now a list of
strings, not numbers, you must wrap each one in quotes:

For Each vItem In .ItemsSelected
strTemp = strTemp & "'" & .ItemData(vItem) & "',"
Next

[Note the two extra single quotes: one in double quotes by itself and one
just before the comma]

It might help if in your next post (I'm sure there *will* be a next one
<smile>) you list the field names and data types of all the fields in your
three tables. Then we will know we're singing from the same hymn sheet
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am so close I can taste it. Both the state and county filters work now.
I am getting this error message when I try to filter on specialtylist.
Syntax error. in query expression '(CSDID in (Select CSDFK from
CSD_Categories where SpecialtiesFK in (PT)))'.

I have a table CSD,Categories and a junction table of CSD_Categories that
holds all the speciatlies (ie PT).

In case you need it... here is the entire code:
Private Sub CommandCSDreport_Click()
Const cAND = " AND "
Const cOR = " OR "
Dim SqlStr As String
Dim sqlWhereString As String
Dim strTemp As String
Dim vItem As Variant

SqlStr = "SELECT * FROM qryCSDwithCategories"
strTemp = ""
With Me.STATE
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "State = '" & .ItemData(vItem) & "'" & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.County
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "County = '" & .ItemData(vItem) & "' " & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.SpecialtyList
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & .ItemData(vItem) & ","
Next
' remove the last comma
strTemp = Left(strTemp, Len(strTemp) - 1)
sqlWhereString = sqlWhereString & "(CSDID in (Select CSDFK from
CSD_Categories " & "where SpecialtiesFK in (" & strTemp & ")))" & cAND
End If
End With

If Len(sqlWhereString) > 0 Then
' remove last AND and append to SQL string
SqlStr = SqlStr & " WHERE " _
& Left(sqlWhereString, Len(sqlWhereString) - Len(cAND))
End If

CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
DoCmd.OpenQuery ("qryCSDReport")

End Sub

Thanks for all your help!!

 

[Graham Mandeno]

Hi Jenny

You can make a number display as Yes/No using a format.

The number format has four parts, separated by semicolons. They are:
1. format for a positive number
2. format for a negative number
3. format for zero
4. format for null

So, for example, a format string like this:

"Yes";"Yes";"No";"No"

will show "Yes" if the number is non-zero, or "No" if it is zero or null.
If you just want to see the yeses, then use:

"Yes";"Yes";" "

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Your not going to believe this... but that was it. It works! Thank you
so
much! Is there a easy way to change the filter from displaying 1's to
Yes's
or something? I know I've taken a lot of your time already, so if your
done
with me let me know. I am very appreciative of all your help!

Hi Jenny

Is SpecialtiesFK a text field or a numeric field?

Is the PK of your Specialties table a text field or a numeric (or
autonumber) field?

[The answer to these should both be the same!!]

If the answer is "numeric" then your combo box properties are wrong.

The RowSource should be:
Select SpecialtyID, SpecialtyName from Specialties order by
SpecialtyName;

ColumnCount should be 2
ColumnWidths should be 0 (this hides the first column)
BoundColumn should be 1

This will give you a list of numbers in your subquery - for example:
(CSDID in (Select CSDFK from CSD_Categories where SpecialtiesFK in
(1,3,5)))

If the answer is "text" then you have a slightly different design from
what
I've suggested, by no matter. Because your "IN" list is now a list of
strings, not numbers, you must wrap each one in quotes:

For Each vItem In .ItemsSelected
strTemp = strTemp & "'" & .ItemData(vItem) & "',"
Next

[Note the two extra single quotes: one in double quotes by itself and one
just before the comma]

It might help if in your next post (I'm sure there *will* be a next one
<smile>) you list the field names and data types of all the fields in
your
three tables. Then we will know we're singing from the same hymn sheet

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am so close I can taste it. Both the state and county filters work
now.
I am getting this error message when I try to filter on specialtylist.
Syntax error. in query expression '(CSDID in (Select CSDFK from
CSD_Categories where SpecialtiesFK in (PT)))'.

I have a table CSD,Categories and a junction table of CSD_Categories
that
holds all the speciatlies (ie PT).

In case you need it... here is the entire code:
Private Sub CommandCSDreport_Click()
Const cAND = " AND "
Const cOR = " OR "
Dim SqlStr As String
Dim sqlWhereString As String
Dim strTemp As String
Dim vItem As Variant

SqlStr = "SELECT * FROM qryCSDwithCategories"
strTemp = ""
With Me.STATE
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "State = '" & .ItemData(vItem) & "'" & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.County
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "County = '" & .ItemData(vItem) & "' " & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.SpecialtyList
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & .ItemData(vItem) & ","
Next
' remove the last comma
strTemp = Left(strTemp, Len(strTemp) - 1)
sqlWhereString = sqlWhereString & "(CSDID in (Select CSDFK from
CSD_Categories " & "where SpecialtiesFK in (" & strTemp & ")))" & cAND
End If
End With

If Len(sqlWhereString) > 0 Then
' remove last AND and append to SQL string
SqlStr = SqlStr & " WHERE " _
& Left(sqlWhereString, Len(sqlWhereString) - Len(cAND))
End If

CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
DoCmd.OpenQuery ("qryCSDReport")

End Sub

Thanks for all your help!!