report from check boxes

G

Guest

I have a table with many fields, but I want to use the following fields to
pull data into a report: State, county, PT, ST, OT, VRE. State & County are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The are
filled in by a check box via form. I'm trying to create a report from a form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties. However, how
can I rewrite this code to be able to choose PT from a dropdown and only show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information], [Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy], Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
 
G

Graham Mandeno

Hi Jenny

You just need to inspect the value of the checkbox and, if it is checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
I have a table with many fields, but I want to use the following fields to
pull data into a report: State, county, PT, ST, OT, VRE. State & County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The are
filled in by a check box via form. I'm trying to create a report from a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties. However,
how
can I rewrite this code to be able to choose PT from a dropdown and only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
 
G

Guest

please forgive me as I am a total hack... but, does this mean that on my
report creator form I would need to show check boxes for each field, (i.e.
PT, OT...). Currently I have it as a combo box based on a query to list all
the available choices.
thanks in advance.

Graham Mandeno said:
Hi Jenny

You just need to inspect the value of the checkbox and, if it is checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
I have a table with many fields, but I want to use the following fields to
pull data into a report: State, county, PT, ST, OT, VRE. State & County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The are
filled in by a check box via form. I'm trying to create a report from a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties. However,
how
can I rewrite this code to be able to choose PT from a dropdown and only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jenny

Don't apologise! We all have to start learning somewhere :)

Would you never want to filter records that were both PT *and* OT, for
example?

Then, sure, you could use a combo box (or even an option group with four
radio buttons).

Using a combo box, for simplicity make the bound column contain the name of
the field ("PT", "ST", etc):
If Not IsNull cboSelectFilter Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & cboSelectFilter & "<>0) AND
"
End If

Just a thought: are these four fields mutually exclusive? In other words,
a single record should not have more than one of the boxes checked? If so,
you should really just use a single field with a text value ("PT", "ST",
etc) or a numeric value (1="PT", 2="ST", etc).
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
please forgive me as I am a total hack... but, does this mean that on my
report creator form I would need to show check boxes for each field, (i.e.
PT, OT...). Currently I have it as a combo box based on a query to list
all
the available choices.
thanks in advance.

Graham Mandeno said:
Hi Jenny

You just need to inspect the value of the checkbox and, if it is checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
I have a table with many fields, but I want to use the following fields
to
pull data into a report: State, county, PT, ST, OT, VRE. State &
County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The are
filled in by a check box via form. I'm trying to create a report from
a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties.
However,
how
can I rewrite this code to be able to choose PT from a dropdown and
only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No, they are not exclusive. There are actually 46 different categories that
I want to filter by and there could be any number of combinations thereof. I
only listed the four in this example for simplicity. I had wanted to make it
that they could choose more than one at a time to filter on, but since there
are so many options will that just make my report take an eternity to run?
What do you suggest?

Graham Mandeno said:
Hi Jenny

Don't apologise! We all have to start learning somewhere :)

Would you never want to filter records that were both PT *and* OT, for
example?

Then, sure, you could use a combo box (or even an option group with four
radio buttons).

Using a combo box, for simplicity make the bound column contain the name of
the field ("PT", "ST", etc):
If Not IsNull cboSelectFilter Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & cboSelectFilter & "<>0) AND
"
End If

Just a thought: are these four fields mutually exclusive? In other words,
a single record should not have more than one of the boxes checked? If so,
you should really just use a single field with a text value ("PT", "ST",
etc) or a numeric value (1="PT", 2="ST", etc).
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
please forgive me as I am a total hack... but, does this mean that on my
report creator form I would need to show check boxes for each field, (i.e.
PT, OT...). Currently I have it as a combo box based on a query to list
all
the available choices.
thanks in advance.

Graham Mandeno said:
Hi Jenny

You just need to inspect the value of the checkbox and, if it is checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a table with many fields, but I want to use the following fields
to
pull data into a report: State, county, PT, ST, OT, VRE. State &
County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The are
filled in by a check box via form. I'm trying to create a report from
a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties.
However,
how
can I rewrite this code to be able to choose PT from a dropdown and
only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jenny

Hmmm... what I *really* suggest is that you take a step back and take a look
at your design.

One of the principle rules of good database design is that you should not
have repeating fields (or groups of fields) serving the same function. For
example, if you have a Customer table and each customer can have a number of
contact people, it might be tempting to add nine fields:
Contact1Name, Contact1Email, Contact1Phone
Contact2Name, Contact2Email, Contact2Phone
Contact3Name, Contact3Email, Contact3Phone

However, this introduces several problems:
- what is a customer has more than three contacts?
- how do you find which customer is associated with a given contact
email?
... and many more besides

Related to this is the rule that you must not store data in field names.
This is effectively what you are doing with your categories in the CSD
table. I note you have field names such as:
[Physical Therapy (PT)]
[Occupational Therapy (OT)]
[Speech Therapy (ST)]
... an so on

These field names are actually data. What are you going to do when someone
decides to add a new category? You will need to add a new field to your
table, and then modify the design of every one of your forms and reports!!

Instead, these categories should be stored in a separate table:
CategoryID or CategoryCode (primary key)
CategoryName

The primary key field could be either an AutoNumber (CategoryID) or a short
text field (e.g. "PT"). (For the purpose of this discussion, let's say you
go for the autonumber option)

Now, you have two tables (CSD and Categories) with a many-to-many
relationship (one CSD can fall into many categories and many CSDs can fall
into one category).

In a relational database, you can have only one-to-one or one-to-many
relationships. To create a many-to-many relationship you need a third
table, called a "junction table".

A junction table is the "many" side of *two* one-to-many relationships, one
with each of the other tables. So your junction table (CSD_Categories)
needs only two fields: CSDID and CategoryID. [Note that your CSD table must
have a primary key - if it does not have one already, add an autonumber
field named "CSDID"]

You then create two one-to-many relationships between tblCSD and
CSD_Categories (with cascade deletes) and between Categories and
CSD_Categories.

Now, each category that a CSD belongs to will have one corresponding record
in the junction table. In effect, each marked checkbox becomes one junction
record.

Now, getting back to your report, you can select the categories from a
multi-select listbox same as the state and county.

Dim vItem as Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 then
For Each vItem in .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories)-1)
sqlWhere = 1
sqlWhereString = sqlWhereString _
& "(CSDID in (Select CSDID from CSD_Categories where CategoryID in
(" _
& sqlCategories & ")) AND "
End If
End With

I think I've given you enough to digest for now! I know it might seem a bit
scary, but believe me, if you get the design right from the start then it
will pay off in the long run :)

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
No, they are not exclusive. There are actually 46 different categories
that
I want to filter by and there could be any number of combinations thereof.
I
only listed the four in this example for simplicity. I had wanted to make
it
that they could choose more than one at a time to filter on, but since
there
are so many options will that just make my report take an eternity to run?
What do you suggest?

Graham Mandeno said:
Hi Jenny

Don't apologise! We all have to start learning somewhere :)

Would you never want to filter records that were both PT *and* OT, for
example?

Then, sure, you could use a combo box (or even an option group with four
radio buttons).

Using a combo box, for simplicity make the bound column contain the name
of
the field ("PT", "ST", etc):
If Not IsNull cboSelectFilter Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & cboSelectFilter & "<>0)
AND
"
End If

Just a thought: are these four fields mutually exclusive? In other
words,
a single record should not have more than one of the boxes checked? If
so,
you should really just use a single field with a text value ("PT", "ST",
etc) or a numeric value (1="PT", 2="ST", etc).
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
please forgive me as I am a total hack... but, does this mean that on
my
report creator form I would need to show check boxes for each field,
(i.e.
PT, OT...). Currently I have it as a combo box based on a query to
list
all
the available choices.
thanks in advance.

:

Hi Jenny

You just need to inspect the value of the checkbox and, if it is
checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a table with many fields, but I want to use the following
fields
to
pull data into a report: State, county, PT, ST, OT, VRE. State &
County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The
are
filled in by a check box via form. I'm trying to create a report
from
a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties.
However,
how
can I rewrite this code to be able to choose PT from a dropdown and
only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)],
[Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive
Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric
Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound
Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy
(Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" &
Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" &
Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Whoa! Thanks... that's a bunch to chew on! I will give it a shot. Thanks
for all your help.


Graham Mandeno said:
Hi Jenny

Hmmm... what I *really* suggest is that you take a step back and take a look
at your design.

One of the principle rules of good database design is that you should not
have repeating fields (or groups of fields) serving the same function. For
example, if you have a Customer table and each customer can have a number of
contact people, it might be tempting to add nine fields:
Contact1Name, Contact1Email, Contact1Phone
Contact2Name, Contact2Email, Contact2Phone
Contact3Name, Contact3Email, Contact3Phone

However, this introduces several problems:
- what is a customer has more than three contacts?
- how do you find which customer is associated with a given contact
email?
... and many more besides

Related to this is the rule that you must not store data in field names.
This is effectively what you are doing with your categories in the CSD
table. I note you have field names such as:
[Physical Therapy (PT)]
[Occupational Therapy (OT)]
[Speech Therapy (ST)]
... an so on

These field names are actually data. What are you going to do when someone
decides to add a new category? You will need to add a new field to your
table, and then modify the design of every one of your forms and reports!!

Instead, these categories should be stored in a separate table:
CategoryID or CategoryCode (primary key)
CategoryName

The primary key field could be either an AutoNumber (CategoryID) or a short
text field (e.g. "PT"). (For the purpose of this discussion, let's say you
go for the autonumber option)

Now, you have two tables (CSD and Categories) with a many-to-many
relationship (one CSD can fall into many categories and many CSDs can fall
into one category).

In a relational database, you can have only one-to-one or one-to-many
relationships. To create a many-to-many relationship you need a third
table, called a "junction table".

A junction table is the "many" side of *two* one-to-many relationships, one
with each of the other tables. So your junction table (CSD_Categories)
needs only two fields: CSDID and CategoryID. [Note that your CSD table must
have a primary key - if it does not have one already, add an autonumber
field named "CSDID"]

You then create two one-to-many relationships between tblCSD and
CSD_Categories (with cascade deletes) and between Categories and
CSD_Categories.

Now, each category that a CSD belongs to will have one corresponding record
in the junction table. In effect, each marked checkbox becomes one junction
record.

Now, getting back to your report, you can select the categories from a
multi-select listbox same as the state and county.

Dim vItem as Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 then
For Each vItem in .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories)-1)
sqlWhere = 1
sqlWhereString = sqlWhereString _
& "(CSDID in (Select CSDID from CSD_Categories where CategoryID in
(" _
& sqlCategories & ")) AND "
End If
End With

I think I've given you enough to digest for now! I know it might seem a bit
scary, but believe me, if you get the design right from the start then it
will pay off in the long run :)

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
No, they are not exclusive. There are actually 46 different categories
that
I want to filter by and there could be any number of combinations thereof.
I
only listed the four in this example for simplicity. I had wanted to make
it
that they could choose more than one at a time to filter on, but since
there
are so many options will that just make my report take an eternity to run?
What do you suggest?

Graham Mandeno said:
Hi Jenny

Don't apologise! We all have to start learning somewhere :)

Would you never want to filter records that were both PT *and* OT, for
example?

Then, sure, you could use a combo box (or even an option group with four
radio buttons).

Using a combo box, for simplicity make the bound column contain the name
of
the field ("PT", "ST", etc):
If Not IsNull cboSelectFilter Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & cboSelectFilter & "<>0)
AND
"
End If

Just a thought: are these four fields mutually exclusive? In other
words,
a single record should not have more than one of the boxes checked? If
so,
you should really just use a single field with a text value ("PT", "ST",
etc) or a numeric value (1="PT", 2="ST", etc).
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

please forgive me as I am a total hack... but, does this mean that on
my
report creator form I would need to show check boxes for each field,
(i.e.
PT, OT...). Currently I have it as a combo box based on a query to
list
all
the available choices.
thanks in advance.

:

Hi Jenny

You just need to inspect the value of the checkbox and, if it is
checked,
add more to your sqlWhereString. For example,

If chkPT <> 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (PT<>0) AND "
End If

Do the same for the other three checkboxes.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a table with many fields, but I want to use the following
fields
to
pull data into a report: State, county, PT, ST, OT, VRE. State &
County
are
a list of all states/counties. PT, ST, OT and VRE are yes/no. The
are
filled in by a check box via form. I'm trying to create a report
from
a
form
based on combo boxes to show selected data.
Right now, the report works for choosing state(s) or counties.
However,
how
can I rewrite this code to be able to choose PT from a dropdown and
only
show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)],
[Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive
Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric
Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound
Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy
(Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM tblCSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" &
Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If



For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" &
Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Graham -
First let me say... thank you...thank you...thank you...thank you...!!! You
have been very helpful. I am running into a run-time error of "Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information], [Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy], Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If

For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If


Dim vItem As Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) - 1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialitesID in (" & sqlCategories & ")) AND "
End If
End With

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

End Sub
 
G

Graham Mandeno

Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place. It should be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction table
as I suggested? I'm impressed that you did it so quickly!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
Graham -
First let me say... thank you...thank you...thank you...thank you...!!!
You
have been very helpful. I am running into a run-time error of "Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If

For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If


Dim vItem As Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) - 1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialitesID in (" & sqlCategories & ")) AND "
End If
End With

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

End Sub
Click to expand...
 
G

Guest

I wouldn't be impressed as I probably did it wrong.
I did create the junction table. Am I supposed to delete the "PT, OT, (46
total fields of specialites) from my original table now that I have the
junction table?
Here is my code now... I am receiving the error, "Syntax Error (missing
operator) in query expression 'specialitesID in ()'.
Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtylistString As String


SqlStr = "SELECT [Facility Name] , [Tax ID], [Street Address], City,
STATE, Zip, County, [Phone #] , [Physical Therapy (PT)], [Occupational
Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB, [HIV/AIDS], [Private
Rooms], [Total Parental Nutrition (TPN)], [Central Lines], [PICC Lines] ,
[Continuous Heparin Infusion], [Intrathecal Medications], Chemotherapy,
[Computerized Clinical Information], [Amputation Recovery], [Peritoneal
Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis, [Alzheimer/ Dementia
Care], TBI, [Complex Care], [Cognitive Therapy], Chemo, [Cardiac Rehab],
[Behavioral Management], Bariatric, [Bariatric Weight Limit], [Wall
Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New and Chronic] ,
Vents, [Adult Day], [Respite Care], MS, [24 hour admissions], Pediatric,
[Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care Team],
[Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)], [Wound
VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD"

For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If


For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If


For i = 0 To Me.SpecialtyList.ListCount - 1
If (Me.SpecialtyList.Selected(i) = True) Then
specialtylistString = specialtylistString & "Specialtylist = '" &
Me.SpecialtyList.Column(0, i) & "' Or "
End If
Next i

If Len(specialtylistString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialtylistString,
Len(specialtylistString) - 4) & ") AND "
End If


Dim vItem As Variant
With SpecialtyList
If Me.SpecialtyList.ItemsSelected.Count > 0 Then
For Each vItem In Me.SpecialtyList.ItemsSelected
sqlCategories = sqlCategories & Me.SpecialtyList.ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) = -1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialtiesID in (" & sqlCategories & "))) AND "
End If
End With

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub




Graham Mandeno said:
Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place. It should be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction table
as I suggested? I'm impressed that you did it so quickly!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
Graham -
First let me say... thank you...thank you...thank you...thank you...!!!
You
have been very helpful. I am running into a run-time error of "Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If

For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If


Dim vItem As Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) - 1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialitesID in (" & sqlCategories & ")) AND "
End If
End With

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

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jenny

Yes, you no longer have any need for the 46 specialty fields. However, if
you already have data in them, then that data must first be transferred to
the new junction table. Let me know if this is the case and I'll tell you
how to do it.

It would help me at this point if you list exactly the names of your three
tables and their fields (not counting the "specialty fields" which will
disappear).

Let's say they are as follows (note that it is recommended you don't use any
spaces or non-alphanumeric characters in object names, including table and
field names):

Table CSD
FacilityID (autonumber)
FacilityName
TaxID
StreetAddress
City
State
Zip
County
Phone

Table Specialties
SpecialtyID (autonumber)
SpecialtyName (e.g. "Physical Therapy"
SpecialtyAbbrev (e.g. "PT") [this might be useful for column headings
etc]

Table CSD_Specialties
FacilityFK (FK stands for "foreign key")
SpecialtyFK

Now, I understand you want to create a query with the CSD details and then
46 extra columns, one for each specialty, like a spreadsheet. To do this
from the normalised structure, you need to use a crosstab query.

You *could* create a crosstab query from scratch in code, but I think it
would be much easier to create one and save it, then filter it for your
report. So, create a query with SQL similar to this:

TRANSFORM Count(CSD_Specialties.FacilityFK)
SELECT CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
FROM CSD LEFT JOIN (Specialties RIGHT JOIN CSD_Specialties
ON Specialties.SpecialtyID = CSD_Specialties.SpecialtyFK)
ON CSD.FacilityID = CSD_Specialties.FacilityFK
GROUP BY CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
PIVOT Specialties.SpecialtyAbbrev;

That *should* give you something that looks very similar to the unnormalised
table that you have at present. Save the query as "qryCSDwithSpecialties".

Now, to generate your filtered view of the query, you just need Select *
with a WHERE clause:

=============== start code ==================
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 qryCSDwithSpecialties"
strTemp = ""
With Me.StateList
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.CountyList
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 _
& "(FacilityID in (Select FacilityFK from CSD_Categories " _
& "where SpecialtyFK 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
======== end code =======================
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jenny said:
I wouldn't be impressed as I probably did it wrong.
I did create the junction table. Am I supposed to delete the "PT, OT, (46
total fields of specialites) from my original table now that I have the
junction table?
Here is my code now... I am receiving the error, "Syntax Error (missing
operator) in query expression 'specialitesID in ()'.
Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtylistString As String


SqlStr = "SELECT [Facility Name] , [Tax ID], [Street Address], City,
STATE, Zip, County, [Phone #] , [Physical Therapy (PT)], [Occupational
Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB, [HIV/AIDS], [Private
Rooms], [Total Parental Nutrition (TPN)], [Central Lines], [PICC Lines] ,
[Continuous Heparin Infusion], [Intrathecal Medications], Chemotherapy,
[Computerized Clinical Information], [Amputation Recovery], [Peritoneal
Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis, [Alzheimer/ Dementia
Care], TBI, [Complex Care], [Cognitive Therapy], Chemo, [Cardiac Rehab],
[Behavioral Management], Bariatric, [Bariatric Weight Limit], [Wall
Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New and
Chronic] ,
Vents, [Adult Day], [Respite Care], MS, [24 hour admissions], Pediatric,
[Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care Team],
[Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound
VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD"

For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If


For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If


For i = 0 To Me.SpecialtyList.ListCount - 1
If (Me.SpecialtyList.Selected(i) = True) Then
specialtylistString = specialtylistString & "Specialtylist = '" &
Me.SpecialtyList.Column(0, i) & "' Or "
End If
Next i

If Len(specialtylistString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialtylistString,
Len(specialtylistString) - 4) & ") AND "
End If


Dim vItem As Variant
With SpecialtyList
If Me.SpecialtyList.ItemsSelected.Count > 0 Then
For Each vItem In Me.SpecialtyList.ItemsSelected
sqlCategories = sqlCategories & Me.SpecialtyList.ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) = -1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialtiesID in (" & sqlCategories & "))) AND "
End If
End With

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub




Graham Mandeno said:
Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't
you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place. It should be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction
table
as I suggested? I'm impressed that you did it so quickly!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
Graham -
First let me say... thank you...thank you...thank you...thank you...!!!
You
have been very helpful. I am running into a run-time error of "Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity
Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD "


For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If

For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If

For i = 0 To Me.Speciality.ListCount - 1
If (Me.Speciality.Selected(i) = True) Then
Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
End If
Next i

If Len(specialityString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
End If

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If


Dim vItem As Variant
With lstSelectCategories
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
sqlCategories = sqlCategories & .ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) - 1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialitesID in (" & sqlCategories & ")) AND "
End If
End With

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, I do need help transferring the data to the junction table.
Here are the tables and fields:
Table CSD
FaciltyName
TaxID
StreetAddress
City
County
State
Zip
Phone#
Products
Notes
CSDID (autonumber)

Table Categories
SpecialtyList
Physical Therapy (PT)
Occupational Therapy (OT)
Speech Therapy (ST)
MRSA
VRE
TB
HIV/AIDS
Private Rooms
Total Parental Nutrition (TPN)
Central Lines
PICC Lines
Continuous Heparin Infusion
Intrathecal Medications
Chemotherapy
Computerized Clinical Information
Amputation Recovery
Peritoneal Dialysis
Hospice
HIV/ARC Dementia
Hemodialysis
Alzheimer/ Dementia Care
TBI
Complex Care
Cognitive Therapy
Chemo
Cardiac Rehab
Behavioral Management
Bariatric
Bariatric Weight Limit
Wall Oxygen/Suction Set-Ups
Weaning/Decannulation
Trach - New and Chronic
Vents
Adult Day
Respite Care
MS
24 hour admissions
Pediatric
Palliative Care
IV Pain Meds
PCA Pumps
Wound Care Team
Whirlpool Therapy (Full Tank)
Whirlpool Therapy (Extremity Tank)
Wound VAC Therapy
Estin/Pulse Lavage:

Table CSD_Categories
CSDID
SpecialtiesID

****What is foreign key? I have these fields as just numbers. Are they
supposed to be something else?

Thanks for all your help!!!



Graham Mandeno said:
Hi Jenny

Yes, you no longer have any need for the 46 specialty fields. However, if
you already have data in them, then that data must first be transferred to
the new junction table. Let me know if this is the case and I'll tell you
how to do it.

It would help me at this point if you list exactly the names of your three
tables and their fields (not counting the "specialty fields" which will
disappear).

Let's say they are as follows (note that it is recommended you don't use any
spaces or non-alphanumeric characters in object names, including table and
field names):

Table CSD
FacilityID (autonumber)
FacilityName
TaxID
StreetAddress
City
State
Zip
County
Phone

Table Specialties
SpecialtyID (autonumber)
SpecialtyName (e.g. "Physical Therapy"
SpecialtyAbbrev (e.g. "PT") [this might be useful for column headings
etc]

Table CSD_Specialties
FacilityFK (FK stands for "foreign key")
SpecialtyFK

Now, I understand you want to create a query with the CSD details and then
46 extra columns, one for each specialty, like a spreadsheet. To do this
from the normalised structure, you need to use a crosstab query.

You *could* create a crosstab query from scratch in code, but I think it
would be much easier to create one and save it, then filter it for your
report. So, create a query with SQL similar to this:

TRANSFORM Count(CSD_Specialties.FacilityFK)
SELECT CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
FROM CSD LEFT JOIN (Specialties RIGHT JOIN CSD_Specialties
ON Specialties.SpecialtyID = CSD_Specialties.SpecialtyFK)
ON CSD.FacilityID = CSD_Specialties.FacilityFK
GROUP BY CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
PIVOT Specialties.SpecialtyAbbrev;

That *should* give you something that looks very similar to the unnormalised
table that you have at present. Save the query as "qryCSDwithSpecialties".

Now, to generate your filtered view of the query, you just need Select *
with a WHERE clause:

=============== start code ==================
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 qryCSDwithSpecialties"
strTemp = ""
With Me.StateList
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.CountyList
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 _
& "(FacilityID in (Select FacilityFK from CSD_Categories " _
& "where SpecialtyFK 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
======== end code =======================
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jenny said:
I wouldn't be impressed as I probably did it wrong.
I did create the junction table. Am I supposed to delete the "PT, OT, (46
total fields of specialites) from my original table now that I have the
junction table?
Here is my code now... I am receiving the error, "Syntax Error (missing
operator) in query expression 'specialitesID in ()'.
Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtylistString As String


SqlStr = "SELECT [Facility Name] , [Tax ID], [Street Address], City,
STATE, Zip, County, [Phone #] , [Physical Therapy (PT)], [Occupational
Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB, [HIV/AIDS], [Private
Rooms], [Total Parental Nutrition (TPN)], [Central Lines], [PICC Lines] ,
[Continuous Heparin Infusion], [Intrathecal Medications], Chemotherapy,
[Computerized Clinical Information], [Amputation Recovery], [Peritoneal
Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis, [Alzheimer/ Dementia
Care], TBI, [Complex Care], [Cognitive Therapy], Chemo, [Cardiac Rehab],
[Behavioral Management], Bariatric, [Bariatric Weight Limit], [Wall
Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New and
Chronic] ,
Vents, [Adult Day], [Respite Care], MS, [24 hour admissions], Pediatric,
[Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care Team],
[Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound
VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD"

For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If


For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If


For i = 0 To Me.SpecialtyList.ListCount - 1
If (Me.SpecialtyList.Selected(i) = True) Then
specialtylistString = specialtylistString & "Specialtylist = '" &
Me.SpecialtyList.Column(0, i) & "' Or "
End If
Next i

If Len(specialtylistString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(specialtylistString,
Len(specialtylistString) - 4) & ") AND "
End If


Dim vItem As Variant
With SpecialtyList
If Me.SpecialtyList.ItemsSelected.Count > 0 Then
For Each vItem In Me.SpecialtyList.ItemsSelected
sqlCategories = sqlCategories & Me.SpecialtyList.ItemData(vItem) & ","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) = -1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialtiesID in (" & sqlCategories & "))) AND "
End If
End With

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub




Graham Mandeno said:
Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't
you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place. It should be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction
table
as I suggested? I'm impressed that you did it so quickly!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham -
First let me say... thank you...thank you...thank you...thank you...!!!
You
have been very helpful. I am running into a run-time error of "Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jenny

First, "what is a foreign key?"

When two tables are related, the relationship is between a unique key
(usually the primary key) in one table, and another field that contains data
of the same type in the second table. That "other" field is called the
"foreign key" (FK). If the FK is itself a unique index (allowing no
duplicates) then the relationship is one-to-one. More commonly, the FK
field does not have this restriction, so the relationship is one-to-many.
Your relationships here, between your CSD and Specialties tables and your
junction table, are one-to-many.

Now, the data type and size of the FK field must always be identical to its
related primary key. The one exception is when the PK is an autonumber. In
this case, the FK must be a long integer (a Number of size "Long").

In my last post I named the fields in the junction table <something>FK to
help you to understand the difference between the primary keys (xxxxID) and
the foreign keys. I'm sorry if you found that confusing :)

OK, so do you have this table?

The first task is to populate the Specialties table. Just type in the
specialty names (and abbreviations if you want to use them) into the table
in datasheet view:

1 Physical Therapy PT
2 Occupational Therapy OT
3 Speech Therapy ST
.... and so on for the 46 records

Note that you don't type in the numbers - they are generated for you
automatically.

Now, to transfer the data you need to create a query to insert a record into
CSD_Specialties for each record in CSD that has [Physical Therapy (PT)]
checked. After running this query, you modify it for the second specialty
and re-run in, and so on.

The SQL of the query needs to look like this:

INSERT INTO CSD_Specialties (CSDFK, SpecialtyFK)
SELECT CSDID, 1 FROM CSD
WHERE [Physical Therapy (PT)] <> 0;

When you run it, it should say "xxx records inserted into table".

Then you change the "1" to "2" and [Occupational Therapy (OT)] and run it
again.

Your junction table will now be fully populated.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jenny said:
Yes, I do need help transferring the data to the junction table.
Here are the tables and fields:
Table CSD
FaciltyName
TaxID
StreetAddress
City
County
State
Zip
Phone#
Products
Notes
CSDID (autonumber)

Table Categories
SpecialtyList
Physical Therapy (PT)
Occupational Therapy (OT)
Speech Therapy (ST)
MRSA
VRE
TB
HIV/AIDS
Private Rooms
Total Parental Nutrition (TPN)
Central Lines
PICC Lines
Continuous Heparin Infusion
Intrathecal Medications
Chemotherapy
Computerized Clinical Information
Amputation Recovery
Peritoneal Dialysis
Hospice
HIV/ARC Dementia
Hemodialysis
Alzheimer/ Dementia Care
TBI
Complex Care
Cognitive Therapy
Chemo
Cardiac Rehab
Behavioral Management
Bariatric
Bariatric Weight Limit
Wall Oxygen/Suction Set-Ups
Weaning/Decannulation
Trach - New and Chronic
Vents
Adult Day
Respite Care
MS
24 hour admissions
Pediatric
Palliative Care
IV Pain Meds
PCA Pumps
Wound Care Team
Whirlpool Therapy (Full Tank)
Whirlpool Therapy (Extremity Tank)
Wound VAC Therapy
Estin/Pulse Lavage:

Table CSD_Categories
CSDID
SpecialtiesID

****What is foreign key? I have these fields as just numbers. Are they
supposed to be something else?

Thanks for all your help!!!



Graham Mandeno said:
Hi Jenny

Yes, you no longer have any need for the 46 specialty fields. However, if
you already have data in them, then that data must first be transferred
to
the new junction table. Let me know if this is the case and I'll tell
you
how to do it.

It would help me at this point if you list exactly the names of your
three
tables and their fields (not counting the "specialty fields" which will
disappear).

Let's say they are as follows (note that it is recommended you don't use
any
spaces or non-alphanumeric characters in object names, including table
and
field names):

Table CSD
FacilityID (autonumber)
FacilityName
TaxID
StreetAddress
City
State
Zip
County
Phone

Table Specialties
SpecialtyID (autonumber)
SpecialtyName (e.g. "Physical Therapy"
SpecialtyAbbrev (e.g. "PT") [this might be useful for column headings
etc]

Table CSD_Specialties
FacilityFK (FK stands for "foreign key")
SpecialtyFK

Now, I understand you want to create a query with the CSD details and
then
46 extra columns, one for each specialty, like a spreadsheet. To do this
from the normalised structure, you need to use a crosstab query.

You *could* create a crosstab query from scratch in code, but I think it
would be much easier to create one and save it, then filter it for your
report. So, create a query with SQL similar to this:

TRANSFORM Count(CSD_Specialties.FacilityFK)
SELECT CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
FROM CSD LEFT JOIN (Specialties RIGHT JOIN CSD_Specialties
ON Specialties.SpecialtyID = CSD_Specialties.SpecialtyFK)
ON CSD.FacilityID = CSD_Specialties.FacilityFK
GROUP BY CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
PIVOT Specialties.SpecialtyAbbrev;

That *should* give you something that looks very similar to the
unnormalised
table that you have at present. Save the query as
"qryCSDwithSpecialties".

Now, to generate your filtered view of the query, you just need Select *
with a WHERE clause:

=============== start code ==================
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 qryCSDwithSpecialties"
strTemp = ""
With Me.StateList
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.CountyList
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 _
& "(FacilityID in (Select FacilityFK from CSD_Categories
" _
& "where SpecialtyFK 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
======== end code =======================
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jenny said:
I wouldn't be impressed as I probably did it wrong.
I did create the junction table. Am I supposed to delete the "PT, OT,
(46
total fields of specialites) from my original table now that I have the
junction table?
Here is my code now... I am receiving the error, "Syntax Error (missing
operator) in query expression 'specialitesID in ()'.
Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtylistString As String


SqlStr = "SELECT [Facility Name] , [Tax ID], [Street Address], City,
STATE, Zip, County, [Phone #] , [Physical Therapy (PT)], [Occupational
Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB, [HIV/AIDS],
[Private
Rooms], [Total Parental Nutrition (TPN)], [Central Lines], [PICC Lines]
,
[Continuous Heparin Infusion], [Intrathecal Medications], Chemotherapy,
[Computerized Clinical Information], [Amputation Recovery], [Peritoneal
Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis, [Alzheimer/
Dementia
Care], TBI, [Complex Care], [Cognitive Therapy], Chemo, [Cardiac
Rehab],
[Behavioral Management], Bariatric, [Bariatric Weight Limit], [Wall
Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New and
Chronic] ,
Vents, [Adult Day], [Respite Care], MS, [24 hour admissions],
Pediatric,
[Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care Team],
[Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound
VAC Therapy] , [Estin/Pulse Lavage:]" & _
"FROM CSD"

For i = 0 To Me.STATE.ListCount - 1
If (Me.STATE.Selected(i) = True) Then
stateString = stateString & "State = '" & Me.STATE.Column(0,
i)
& "' Or "
End If
Next i

If Len(stateString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
End If


For i = 0 To Me.County.ListCount - 1
If (Me.County.Selected(i) = True) Then
countyString = countyString & "County = '" &
Me.County.Column(0,
i) & "' Or "
End If
Next i

If Len(countyString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
End If


For i = 0 To Me.SpecialtyList.ListCount - 1
If (Me.SpecialtyList.Selected(i) = True) Then
specialtylistString = specialtylistString & "Specialtylist =
'" &
Me.SpecialtyList.Column(0, i) & "' Or "
End If
Next i

If Len(specialtylistString) > 0 Then
sqlWhereTrue = 1
sqlWhereString = sqlWhereString & " (" &
Left(specialtylistString,
Len(specialtylistString) - 4) & ") AND "
End If


Dim vItem As Variant
With SpecialtyList
If Me.SpecialtyList.ItemsSelected.Count > 0 Then
For Each vItem In Me.SpecialtyList.ItemsSelected
sqlCategories = sqlCategories & Me.SpecialtyList.ItemData(vItem) &
","
Next
' remove the last comma
sqlCategories = Left(sqlCategories, Len(sqlCategories) = -1)
sqlWhere = 1
sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialtiesID in (" & sqlCategories & "))) AND "
End If
End With

If sqlWhereTrue = 1 Then
SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
End If

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

End Sub




:

Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't
you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place. It should
be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction
table
as I suggested? I'm impressed that you did it so quickly!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham -
First let me say... thank you...thank you...thank you...thank
you...!!!
You
have been very helpful. I am running into a run-time error of
"Object
Required" on this line of the code: .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()

Dim dbs As Database, SqlStr As String
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCSDReport")
Dim sqlWhereString As String
Dim sqlWhereTrue As Integer
Dim sqlStart As String
Dim stateString As String
Dim countyString As String
Dim specialtyString As String



SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)],
[Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information],
[Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia],
Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive
Therapy],
Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric
Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation],
[Trach -
New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
admissions],
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Graham!
I got the junction table filled.
I'm working on the code in the filtered view of the query and running into
an error. I pasted in your code, but I get a "compile error: Method or data
member not found" for Me.State. You had "Me.Statelist" in your code, but I
don't have a field called this so I tried just Me.State. and still get the
above error.

Any thoughts?
 
G

Graham Mandeno

Hi Jenny

This should be the name of the *listbox* where you select the state(s) you
wish to filter on. It has nothing to do with the name of the field in your
CSD table. In fact, I think you should give it a different name to avoid
confusion, for example, "lstStateFilter".
 
G

Guest

duh, I should have known that. sorry.
Now, I'm getting a syntax error on:
sqlWhereString = sqlWhereString & "("strTemp & ")" & cAND

any thoughts why?
 
D

Douglas J. Steele

You're missing an ampersand between the opening parenthesis and the word
strTemp:

sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
 
G

Guest

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!!
 
D

Douglas J. Steele

Since what's being shown as a value for SpecialtiesPT is text, presumably
you need

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
 
G

Graham Mandeno

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 :)
 

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

Running query with input from list boxes 5
WHERE clause in query 4
open report based on several mulit select list boxes causes error7769 5
Open a report based on values from Two List boxes value 2
select child records from list box to filter report 1
Runtime error 2001 6
QBF on Steroids with List Control --almost there, but problem 3
Report Preview and RTF discrepancy 2

Top