Multi-select list box as query parameter

[Cathleen]

I have read several posts about using a multi-select list box as a query
parameter and am now trying to adapt what I've found to my database. But, I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled], which is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in the list
box, but if more than one is selected the query doesn't return any records. I
think this might be because the name of each waterbody from the list box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when listed in the
hidden text box, but I cannot figure out how to get the output to look like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Roger Carlson]

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb " which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Cathleen]

Thank you for the advice. I checked out your example, but I don't know how to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is not
something I know how to do programmatically. Is there any way to make it work
without recreating the query?
Thanks again!

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb " which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have read several posts about using a multi-select list box as a query
parameter and am now trying to adapt what I've found to my database. But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled], which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in the
list
box, but if more than one is selected the query doesn't return any
records. I
think this might be because the name of each waterbody from the list box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when listed in
the
hidden text box, but I cannot figure out how to get the output to look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Roger Carlson]

That depends on your SQL Statement. It is possible to pull the SQL from an
existing query, then programmatically add your listbox to the Where clause.
It would help if the WHERE is at the end of the statement.

For instance, suppose your query looked like this:

SELECT tblStaff.FirstName, tblStaff.LastName, tblCaseData.PatientLastName,
tblCaseData.PatientFirstName, tblCaseData.DcDate
FROM tblStaff INNER JOIN tblCaseData ON tblStaff.StaffID =
tblCaseData.StaffID
WHERE (((tblCaseData.DcDate)>#1/31/2002#));

(It doesn't matter what the actual query is as long as your Where clause is
the last thing.)

You could pull the SQL from the query like this:

Dim varQDef As QueryDef
Dim strSQL As String
Dim strWhere As String

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end of the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:

strWhere = " AND waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere


so "AND waterbody_compiled IN ('Waldo Lake', 'Blue Lake')" will be added to
your SQL string.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the advice. I checked out your example, but I don't know how
to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is not
something I know how to do programmatically. Is there any way to make it
work
without recreating the query?
Thanks again!

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb " which illustrates how to do this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See
Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have read several posts about using a multi-select list box as a query
parameter and am now trying to adapt what I've found to my database.
But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled],
which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in the
list
box, but if more than one is selected the query doesn't return any
records. I
think this might be because the name of each waterbody from the list
box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when listed
in
the
hidden text box, but I cannot figure out how to get the output to look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Cathleen]

O.K. I'm trying to add the listbox to the WHERE clause as you suggested, but
I have another question, as I haven't been able to make anything happen? So,
should I be putting this code in the OnClick event for the command button
that runs my query? Previously, I had a hidden text box (which was referenced
in the query's criteria) in which I was trying to compile the selections from
the listbox [waterbody_select], and I'm not clear on whether that is still
needed.

Sorry if I'm a bit confused - this is all new territory for me!

Thank you again. I really appreciate all of your assistance.
Cathleen

That depends on your SQL Statement. It is possible to pull the SQL from an
existing query, then programmatically add your listbox to the Where clause.
It would help if the WHERE is at the end of the statement.

For instance, suppose your query looked like this:

SELECT tblStaff.FirstName, tblStaff.LastName, tblCaseData.PatientLastName,
tblCaseData.PatientFirstName, tblCaseData.DcDate
FROM tblStaff INNER JOIN tblCaseData ON tblStaff.StaffID =
tblCaseData.StaffID
WHERE (((tblCaseData.DcDate)>#1/31/2002#));

(It doesn't matter what the actual query is as long as your Where clause is
the last thing.)

You could pull the SQL from the query like this:

Dim varQDef As QueryDef
Dim strSQL As String
Dim strWhere As String

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end of the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:

strWhere = " AND waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere


so "AND waterbody_compiled IN ('Waldo Lake', 'Blue Lake')" will be added to
your SQL string.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the advice. I checked out your example, but I don't know how
to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is not
something I know how to do programmatically. Is there any way to make it
work
without recreating the query?
Thanks again!

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb " which illustrates how to do this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See
Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have read several posts about using a multi-select list box as a query
parameter and am now trying to adapt what I've found to my database.
But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled],
which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in the
list
box, but if more than one is selected the query doesn't return any
records. I
think this might be because the name of each waterbody from the list
box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when listed
in
the
hidden text box, but I cannot figure out how to get the output to look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Roger Carlson]

All I've been concentrating on is building the SQL statement. You haven't
said what exactly you want to happen. Do you just want to open the query,
that is, display it on the screen?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

O.K. I'm trying to add the listbox to the WHERE clause as you suggested,
but
I have another question, as I haven't been able to make anything happen?
So,
should I be putting this code in the OnClick event for the command button
that runs my query? Previously, I had a hidden text box (which was
referenced
in the query's criteria) in which I was trying to compile the selections
from
the listbox [waterbody_select], and I'm not clear on whether that is still
needed.

Sorry if I'm a bit confused - this is all new territory for me!

Thank you again. I really appreciate all of your assistance.
Cathleen

That depends on your SQL Statement. It is possible to pull the SQL from
an
existing query, then programmatically add your listbox to the Where
clause.
It would help if the WHERE is at the end of the statement.

For instance, suppose your query looked like this:

SELECT tblStaff.FirstName, tblStaff.LastName,
tblCaseData.PatientLastName,
tblCaseData.PatientFirstName, tblCaseData.DcDate
FROM tblStaff INNER JOIN tblCaseData ON tblStaff.StaffID =
tblCaseData.StaffID
WHERE (((tblCaseData.DcDate)>#1/31/2002#));

(It doesn't matter what the actual query is as long as your Where clause
is
the last thing.)

You could pull the SQL from the query like this:

Dim varQDef As QueryDef
Dim strSQL As String
Dim strWhere As String

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end of
the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:

strWhere = " AND waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere


so "AND waterbody_compiled IN ('Waldo Lake', 'Blue Lake')" will be added
to
your SQL string.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the advice. I checked out your example, but I don't know
how
to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is
not
something I know how to do programmatically. Is there any way to make
it
work
without recreating the query?
Thanks again!

:

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) &
"', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "CreateQueries2.mdb " which illustrates how to do this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See
Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have read several posts about using a multi-select list box as a
query
parameter and am now trying to adapt what I've found to my database.
But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled],
which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in
the
list
box, but if more than one is selected the query doesn't return any
records. I
think this might be because the name of each waterbody from the list
box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when
listed
in
the
hidden text box, but I cannot figure out how to get the output to
look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Cathleen]

My intent was to use my form to set parameters for an existing query and open
it. I have text boxes on my form (that are working well) to set date
parameters but I need to be able to limit query results to one or more
"waterbodies" - that's what the multi-select list box is for. So, if I build
the SQL statement from the list box selections and open the query, what
happens to the date parameters I have set up?

Hope that makes sense - sorry I didn't explain myself better the first time.

Cathleen

All I've been concentrating on is building the SQL statement. You haven't
said what exactly you want to happen. Do you just want to open the query,
that is, display it on the screen?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

O.K. I'm trying to add the listbox to the WHERE clause as you suggested,
but
I have another question, as I haven't been able to make anything happen?
So,
should I be putting this code in the OnClick event for the command button
that runs my query? Previously, I had a hidden text box (which was
referenced
in the query's criteria) in which I was trying to compile the selections
from
the listbox [waterbody_select], and I'm not clear on whether that is still
needed.

Sorry if I'm a bit confused - this is all new territory for me!

Thank you again. I really appreciate all of your assistance.
Cathleen

That depends on your SQL Statement. It is possible to pull the SQL from
an
existing query, then programmatically add your listbox to the Where
clause.
It would help if the WHERE is at the end of the statement.

For instance, suppose your query looked like this:

SELECT tblStaff.FirstName, tblStaff.LastName,
tblCaseData.PatientLastName,
tblCaseData.PatientFirstName, tblCaseData.DcDate
FROM tblStaff INNER JOIN tblCaseData ON tblStaff.StaffID =
tblCaseData.StaffID
WHERE (((tblCaseData.DcDate)>#1/31/2002#));

(It doesn't matter what the actual query is as long as your Where clause
is
the last thing.)

You could pull the SQL from the query like this:

Dim varQDef As QueryDef
Dim strSQL As String
Dim strWhere As String

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end of
the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:

strWhere = " AND waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere


so "AND waterbody_compiled IN ('Waldo Lake', 'Blue Lake')" will be added
to
your SQL string.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Thank you for the advice. I checked out your example, but I don't know
how
to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is
not
something I know how to do programmatically. Is there any way to make
it
work
without recreating the query?
Thanks again!

:

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) &
"', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "CreateQueries2.mdb " which illustrates how to do this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368 (See
Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have read several posts about using a multi-select list box as a
query
parameter and am now trying to adapt what I've found to my database.
But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form called
SelectForQuery. I also have a hidden text box [waterbody_compiled],
which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected in
the
list
box, but if more than one is selected the query doesn't return any
records. I
think this might be because the name of each waterbody from the list
box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when
listed
in
the
hidden text box, but I cannot figure out how to get the output to
look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Roger Carlson]

Okay, suppose you had a button on your form called "cmdRunQuery" and in its
OnClick event, you put code like this:

'-------------------
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim varQDef As QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end of the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:
strWhere = " AND waterbody_compiled IN( "

For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "', "
End If
Next i

strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

'*** delete the previous query
'*** and set recreate with new SQL string
db.QueryDefs.Delete "MyQuery"
Set varQDef = db.CreateQueryDef("MyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "MyQuery", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub
'-------------------

Every place "MyQuery" appears in the above code, replace it with your actual
saved query name.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

My intent was to use my form to set parameters for an existing query and
open
it. I have text boxes on my form (that are working well) to set date
parameters but I need to be able to limit query results to one or more
"waterbodies" - that's what the multi-select list box is for. So, if I
build
the SQL statement from the list box selections and open the query, what
happens to the date parameters I have set up?

Hope that makes sense - sorry I didn't explain myself better the first
time.

Cathleen

All I've been concentrating on is building the SQL statement. You
haven't
said what exactly you want to happen. Do you just want to open the
query,
that is, display it on the screen?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

O.K. I'm trying to add the listbox to the WHERE clause as you
suggested,
but
I have another question, as I haven't been able to make anything
happen?
So,
should I be putting this code in the OnClick event for the command
button
that runs my query? Previously, I had a hidden text box (which was
referenced
in the query's criteria) in which I was trying to compile the
selections
from
the listbox [waterbody_select], and I'm not clear on whether that is
still
needed.

Sorry if I'm a bit confused - this is all new territory for me!

Thank you again. I really appreciate all of your assistance.
Cathleen

:

That depends on your SQL Statement. It is possible to pull the SQL
from
an
existing query, then programmatically add your listbox to the Where
clause.
It would help if the WHERE is at the end of the statement.

For instance, suppose your query looked like this:

SELECT tblStaff.FirstName, tblStaff.LastName,
tblCaseData.PatientLastName,
tblCaseData.PatientFirstName, tblCaseData.DcDate
FROM tblStaff INNER JOIN tblCaseData ON tblStaff.StaffID =
tblCaseData.StaffID
WHERE (((tblCaseData.DcDate)>#1/31/2002#));

(It doesn't matter what the actual query is as long as your Where
clause
is
the last thing.)

You could pull the SQL from the query like this:

Dim varQDef As QueryDef
Dim strSQL As String
Dim strWhere As String

Set varQDef = CurrentDb.QueryDefs("MyQuery") ' use your actual
query
name here
strSQL = varQDef.SQL

'Remove carriage return line feed and the semi colon from the end
of
the
string.
strSQL = Left(strSQL, Len(strSQL) - 3)

'then add the loop to read the listbox:

strWhere = " AND waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "',
"
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere


so "AND waterbody_compiled IN ('Waldo Lake', 'Blue Lake')" will be
added
to
your SQL string.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Thank you for the advice. I checked out your example, but I don't
know
how
to
make something like that work in my situation. I'm trying to use the
multi-select list box (along with some text boxes for dates) to set
parameters for an existing query. The query is rather complex and is
not
something I know how to do programmatically. Is there any way to
make
it
work
without recreating the query?
Thanks again!

:

Well, I would do it something like this:

strSQL = "SELECT * FROM YourTable "
strWhere = "Where waterbody_compiled IN( "
For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) &
"', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

The IN construct will look something like:

waterbody_compiled IN ('Waldo Lake', 'Blue Lake')

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "CreateQueries2.mdb " which illustrates how to do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368
(See
Form
6).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I have read several posts about using a multi-select list box as a
query
parameter and am now trying to adapt what I've found to my
database.
But,
I
could use a little help with the code.

I have a multi-select list box [waterbody_select] on a form
called
SelectForQuery. I also have a hidden text box
[waterbody_compiled],
which
is
referenced as the criteria for my query.

I have the following code in AfterUpdate event of the list box
[waterbody_select]. All works well if only one item is selected
in
the
list
box, but if more than one is selected the query doesn't return
any
records. I
think this might be because the name of each waterbody from the
list
box
needs to be in quotes (e.g., "Waldo Lake" OR "Blue Lake") when
listed
in
the
hidden text box, but I cannot figure out how to get the output to
look
like
that. Or, maybe the problem isn't so simple...

Here's the code I'm using:

Private Sub waterbody_select_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Forms![SelectForQuery1]
Set ctl = frm![waterbody_select]

Me.waterbody_compiled = ""

For Each varItem In ctl.ItemsSelected
Me.waterbody_compiled = Me.waterbody_compiled &
ctl.ItemData(varItem) & " OR "
Next varItem

Me.waterbody_compiled = Left(Me.waterbody_compiled,
Len(Me.waterbody_compiled) - 4)
End Sub


Any advice would be greatly appreciated.
Thank you!
Cathleen

 

[Cathleen]

Roger,
Thanks for all of your help with this. I think I'm getting close, but still
have a problem. I put the code in the OnClick event of my command button, but
I get the following error message:
Syntax Error in string in query expression
'(((ChemDepth_Union.sampling.date) Between Forms!SelectForQuery1!startdate
AND Forms!SelectForQuery1!enddate)) AND (ChemDepth_Union.name_waterbody) IN
('Blue Lake','Elizabeth Lake');'.

Here's the code up to that point:
Private Sub cmdRunChemall_Click()
On Error GoTo Err_cmdRunChemall_Click

Dim db As DAO.Database
Dim varQDef As QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

Set varQDef = CurrentDb.QueryDefs("ChemAll_copy")
strSQL = varQDef.SQL

strSQL = Left(strSQL, Len(strSQL) - 3)
strWhere = " AND (ChemDepth_Union.name_waterbody) IN( "

For i = 0 To waterbody_select.ListCount - 1
If waterbody_select.Selected(i) Then
strWhere = strWhere & "'" & waterbody_select.Column(0, i) & "',"
End If
Next i

strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere

But, if I put this directly in the query's SQL statement, the query works
perfectly:
WHERE (((ChemDepth_Union.sampling_date) Between
Forms!SelectForQuery1!startdate And Forms!SelectForQuery1!enddate)) AND
(ChemDepth_Union.name_waterbody) IN('Blue Lake','Elizabeth Lake');

Any ideas about what is going on?
Thanks again!
Cathleen

 

[Roger Carlson]

The only thing I see off hand is in your Syntax Error it says:

ChemDepth_Union.sampling.date

rather than

ChemDepth_Union.sampling_date

Was this just a typo in your question or was that actually in the error
message?

 

[Cathleen]

That was a typo in my post. The error message reads
ChemDepth_Union.sampling_date, which should be correct.

 

[Roger Carlson]

Okay, backing up to this again. Do me a favor, and after this line:

strSQL = strSQL & strWhere

add

debug.print strSQL

and set a BreakPoint on the line that follows. Run the code. This will
write your entire SQL statement to the Immediate Window. Copy it and post
it here.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/

 

[Cathleen]

Here's the whole SQL statement. I noticed that the last item in the WHERE
clause doesn't have a quote at the end of it. If that is the problem, how do
I get it there?

SELECT ChemDepth_Union.name_waterbody AS Lake, ChemDepth_Union.station_name,
ChemDepth_Union.sampling_date, ChemDepth_Union.sample_type_name,
ChemDepth_Union.depth_chemalkph AS Depth, ChemAlkpH_Info.ph,
ChemAlkpH_Info.ph_note, ChemAlkpH_Info.alk, ChemAlkpH_Info.alku,
ChemNitrogenSi_Info.utn, ChemNitrogenSi_Info.no3n,
ChemNitrogenSi_Info.no3n_note, ChemNitrogenSi_Info.nh3n,
ChemNitrogenSi_Info.nh3n_note, ChemNitrogenSi_Info.si, ChemCaMgKNa_Info.na,
ChemCaMgKNa_Info.k, ChemCaMgKNa_Info.ca, ChemCaMgKNa_Info.mg,
ChemClSO4_Info.so4s, ChemClSO4_Info.so4s_note, ChemClSO4_Info.cl,
ChemConductivity_Info.cond, ChemConductivity_Info.cond_note,
ChemDsolids_Info.dsol, ChemDsolids_Info.dsol_note, ChemPhosKjN_Info.utp,
ChemPhosKjN_Info.utp_note, ChemPhosKjN_Info.tp, ChemPhosKjN_Info.tp_note,
ChemPhosKjN_Info.op, ChemPhosKjN_Info.op_note, ChemPhosKjN_Info.utkn
FROM ChemPhosKjN_Info RIGHT JOIN (ChemDsolids_Info RIGHT JOIN
(ChemConductivity_Info RIGHT JOIN (ChemClSO4_Info RIGHT JOIN
(ChemCaMgKNa_Info RIGHT JOIN (ChemAlkpH_Info RIGHT JOIN (ChemNitrogenSi_Info
RIGHT JOIN ChemDepth_Union ON (ChemNitrogenSi_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemNitrogenSi_Info.station_name =
ChemDepth_Union.station_name) AND (ChemNitrogenSi_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemNitrogenSi_Info.depth_chemnitrogensi
= ChemDepth_Union.depth_chemalkph) AND (ChemNitrogenSi_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemAlkpH_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemAlkpH_Info.station_name =
ChemDepth_Union.station_name) AND (ChemAlkpH_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemAlkpH_Info.depth_chemalkph =
ChemDepth_Union.depth_chemalkph) AND (ChemAlkpH_Info.sample_type_name =
ChemDepth_Union.sample_type_name)) ON (ChemCaMgKNa_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (C
hemCaMgKNa_Info.station_name = ChemDepth_Union.station_name) AND
(ChemCaMgKNa_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemCaMgKNa_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemCaMgKNa_Info.depth_chemcamgkna = ChemDepth_Union.depth_chemalkph)) ON
(ChemClSO4_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemClSO4_Info.station_name = ChemDepth_Union.station_name) AND
(ChemClSO4_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemClSO4_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemClSO4_Info.depth_chemclso4 = ChemDepth_Union.depth_chemalkph)) ON
(ChemConductivity_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemConductivity_Info.station_name = ChemDepth_Union.station_name) AND
(ChemConductivity_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemConductivity_Info.depth_chemconductivity =
ChemDepth_Union.depth_chemalkph) AND (ChemConductivity_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemDs
olids_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemDsolids_Info.station_name = ChemDepth_Union.station_name) AND
(ChemDsolids_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemDsolids_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemDsolids_Info.depth_chemdsolids = ChemDepth_Union.depth_chemalkph)) ON
(ChemPhosKjN_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemPhosKjN_Info.station_name = ChemDepth_Union.station_name) AND
(ChemPhosKjN_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemPhosKjN_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemPhosKjN_Info.depth_chemphoskjn = ChemDepth_Union.depth_chemalkph)
WHERE (((ChemDepth_Union.sampling_date) Between
[Forms]![SelectForQuery1]![startdate] And
[Forms]![SelectForQuery1]![enddate])) AND (ChemDepth_Union.name_waterbody)
IN);
SELECT ChemDepth_Union.name_waterbody AS Lake, ChemDepth_Union.station_name,
ChemDepth_Union.sampling_date, ChemDepth_Union.sample_type_name,
ChemDepth_Union.depth_chemalkph AS Depth, ChemAlkpH_Info.ph,
ChemAlkpH_Info.ph_note, ChemAlkpH_Info.alk, ChemAlkpH_Info.alku,
ChemNitrogenSi_Info.utn, ChemNitrogenSi_Info.no3n,
ChemNitrogenSi_Info.no3n_note, ChemNitrogenSi_Info.nh3n,
ChemNitrogenSi_Info.nh3n_note, ChemNitrogenSi_Info.si, ChemCaMgKNa_Info.na,
ChemCaMgKNa_Info.k, ChemCaMgKNa_Info.ca, ChemCaMgKNa_Info.mg,
ChemClSO4_Info.so4s, ChemClSO4_Info.so4s_note, ChemClSO4_Info.cl,
ChemConductivity_Info.cond, ChemConductivity_Info.cond_note,
ChemDsolids_Info.dsol, ChemDsolids_Info.dsol_note, ChemPhosKjN_Info.utp,
ChemPhosKjN_Info.utp_note, ChemPhosKjN_Info.tp, ChemPhosKjN_Info.tp_note,
ChemPhosKjN_Info.op, ChemPhosKjN_Info.op_note, ChemPhosKjN_Info.utkn
FROM ChemPhosKjN_Info RIGHT JOIN (ChemDsolids_Info RIGHT JOIN
(ChemConductivity_Info RIGHT JOIN (ChemClSO4_Info RIGHT JOIN
(ChemCaMgKNa_Info RIGHT JOIN (ChemAlkpH_Info RIGHT JOIN (ChemNitrogenSi_Info
RIGHT JOIN ChemDepth_Union ON (ChemNitrogenSi_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemNitrogenSi_Info.station_name =
ChemDepth_Union.station_name) AND (ChemNitrogenSi_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemNitrogenSi_Info.depth_chemnitrogensi
= ChemDepth_Union.depth_chemalkph) AND (ChemNitrogenSi_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemAlkpH_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemAlkpH_Info.station_name =
ChemDepth_Union.station_name) AND (ChemAlkpH_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemAlkpH_Info.depth_chemalkph =
ChemDepth_Union.depth_chemalkph) AND (ChemAlkpH_Info.sample_type_name =
ChemDepth_Union.sample_type_name)) ON (ChemCaMgKNa_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (C
hemCaMgKNa_Info.station_name = ChemDepth_Union.station_name) AND
(ChemCaMgKNa_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemCaMgKNa_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemCaMgKNa_Info.depth_chemcamgkna = ChemDepth_Union.depth_chemalkph)) ON
(ChemClSO4_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemClSO4_Info.station_name = ChemDepth_Union.station_name) AND
(ChemClSO4_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemClSO4_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemClSO4_Info.depth_chemclso4 = ChemDepth_Union.depth_chemalkph)) ON
(ChemConductivity_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemConductivity_Info.station_name = ChemDepth_Union.station_name) AND
(ChemConductivity_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemConductivity_Info.depth_chemconductivity =
ChemDepth_Union.depth_chemalkph) AND (ChemConductivity_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemDs
olids_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemDsolids_Info.station_name = ChemDepth_Union.station_name) AND
(ChemDsolids_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemDsolids_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemDsolids_Info.depth_chemdsolids = ChemDepth_Union.depth_chemalkph)) ON
(ChemPhosKjN_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemPhosKjN_Info.station_name = ChemDepth_Union.station_name) AND
(ChemPhosKjN_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemPhosKjN_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemPhosKjN_Info.depth_chemphoskjn = ChemDepth_Union.depth_chemalkph)
WHERE (((ChemDepth_Union.sampling_date) Between
[Forms]![SelectForQuery1]![startdate] And
[Forms]![SelectForQuery1]![enddate])) AND (ChemDepth_Union.name_waterbody)
IN( 'Blue Lake','Elizabeth Lake);

 

[Roger Carlson]

Play with the number here:

strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

I think "1" should do it:

strWhere = Left(strWhere, Len(strWhere) - 1) & ");"


--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/

Here's the whole SQL statement. I noticed that the last item in the WHERE
clause doesn't have a quote at the end of it. If that is the problem, how
do
I get it there?

SELECT ChemDepth_Union.name_waterbody AS Lake,
ChemDepth_Union.station_name,
ChemDepth_Union.sampling_date, ChemDepth_Union.sample_type_name,
ChemDepth_Union.depth_chemalkph AS Depth, ChemAlkpH_Info.ph,
ChemAlkpH_Info.ph_note, ChemAlkpH_Info.alk, ChemAlkpH_Info.alku,
ChemNitrogenSi_Info.utn, ChemNitrogenSi_Info.no3n,
ChemNitrogenSi_Info.no3n_note, ChemNitrogenSi_Info.nh3n,
ChemNitrogenSi_Info.nh3n_note, ChemNitrogenSi_Info.si,
ChemCaMgKNa_Info.na,
ChemCaMgKNa_Info.k, ChemCaMgKNa_Info.ca, ChemCaMgKNa_Info.mg,
ChemClSO4_Info.so4s, ChemClSO4_Info.so4s_note, ChemClSO4_Info.cl,
ChemConductivity_Info.cond, ChemConductivity_Info.cond_note,
ChemDsolids_Info.dsol, ChemDsolids_Info.dsol_note, ChemPhosKjN_Info.utp,
ChemPhosKjN_Info.utp_note, ChemPhosKjN_Info.tp, ChemPhosKjN_Info.tp_note,
ChemPhosKjN_Info.op, ChemPhosKjN_Info.op_note, ChemPhosKjN_Info.utkn
FROM ChemPhosKjN_Info RIGHT JOIN (ChemDsolids_Info RIGHT JOIN
(ChemConductivity_Info RIGHT JOIN (ChemClSO4_Info RIGHT JOIN
(ChemCaMgKNa_Info RIGHT JOIN (ChemAlkpH_Info RIGHT JOIN
(ChemNitrogenSi_Info
RIGHT JOIN ChemDepth_Union ON (ChemNitrogenSi_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemNitrogenSi_Info.station_name =
ChemDepth_Union.station_name) AND (ChemNitrogenSi_Info.sampling_date =
ChemDepth_Union.sampling_date) AND
(ChemNitrogenSi_Info.depth_chemnitrogensi
= ChemDepth_Union.depth_chemalkph) AND
(ChemNitrogenSi_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemAlkpH_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemAlkpH_Info.station_name =
ChemDepth_Union.station_name) AND (ChemAlkpH_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemAlkpH_Info.depth_chemalkph =
ChemDepth_Union.depth_chemalkph) AND (ChemAlkpH_Info.sample_type_name =
ChemDepth_Union.sample_type_name)) ON (ChemCaMgKNa_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (C
hemCaMgKNa_Info.station_name = ChemDepth_Union.station_name) AND
(ChemCaMgKNa_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemCaMgKNa_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemCaMgKNa_Info.depth_chemcamgkna = ChemDepth_Union.depth_chemalkph)) ON
(ChemClSO4_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemClSO4_Info.station_name = ChemDepth_Union.station_name) AND
(ChemClSO4_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemClSO4_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemClSO4_Info.depth_chemclso4 = ChemDepth_Union.depth_chemalkph)) ON
(ChemConductivity_Info.name_waterbody = ChemDepth_Union.name_waterbody)
AND
(ChemConductivity_Info.station_name = ChemDepth_Union.station_name) AND
(ChemConductivity_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemConductivity_Info.depth_chemconductivity =
ChemDepth_Union.depth_chemalkph) AND
(ChemConductivity_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemDs
olids_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemDsolids_Info.station_name = ChemDepth_Union.station_name) AND
(ChemDsolids_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemDsolids_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemDsolids_Info.depth_chemdsolids = ChemDepth_Union.depth_chemalkph)) ON
(ChemPhosKjN_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemPhosKjN_Info.station_name = ChemDepth_Union.station_name) AND
(ChemPhosKjN_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemPhosKjN_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemPhosKjN_Info.depth_chemphoskjn = ChemDepth_Union.depth_chemalkph)
WHERE (((ChemDepth_Union.sampling_date) Between
[Forms]![SelectForQuery1]![startdate] And
[Forms]![SelectForQuery1]![enddate])) AND (ChemDepth_Union.name_waterbody)
IN);
SELECT ChemDepth_Union.name_waterbody AS Lake,
ChemDepth_Union.station_name,
ChemDepth_Union.sampling_date, ChemDepth_Union.sample_type_name,
ChemDepth_Union.depth_chemalkph AS Depth, ChemAlkpH_Info.ph,
ChemAlkpH_Info.ph_note, ChemAlkpH_Info.alk, ChemAlkpH_Info.alku,
ChemNitrogenSi_Info.utn, ChemNitrogenSi_Info.no3n,
ChemNitrogenSi_Info.no3n_note, ChemNitrogenSi_Info.nh3n,
ChemNitrogenSi_Info.nh3n_note, ChemNitrogenSi_Info.si,
ChemCaMgKNa_Info.na,
ChemCaMgKNa_Info.k, ChemCaMgKNa_Info.ca, ChemCaMgKNa_Info.mg,
ChemClSO4_Info.so4s, ChemClSO4_Info.so4s_note, ChemClSO4_Info.cl,
ChemConductivity_Info.cond, ChemConductivity_Info.cond_note,
ChemDsolids_Info.dsol, ChemDsolids_Info.dsol_note, ChemPhosKjN_Info.utp,
ChemPhosKjN_Info.utp_note, ChemPhosKjN_Info.tp, ChemPhosKjN_Info.tp_note,
ChemPhosKjN_Info.op, ChemPhosKjN_Info.op_note, ChemPhosKjN_Info.utkn
FROM ChemPhosKjN_Info RIGHT JOIN (ChemDsolids_Info RIGHT JOIN
(ChemConductivity_Info RIGHT JOIN (ChemClSO4_Info RIGHT JOIN
(ChemCaMgKNa_Info RIGHT JOIN (ChemAlkpH_Info RIGHT JOIN
(ChemNitrogenSi_Info
RIGHT JOIN ChemDepth_Union ON (ChemNitrogenSi_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemNitrogenSi_Info.station_name =
ChemDepth_Union.station_name) AND (ChemNitrogenSi_Info.sampling_date =
ChemDepth_Union.sampling_date) AND
(ChemNitrogenSi_Info.depth_chemnitrogensi
= ChemDepth_Union.depth_chemalkph) AND
(ChemNitrogenSi_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemAlkpH_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (ChemAlkpH_Info.station_name =
ChemDepth_Union.station_name) AND (ChemAlkpH_Info.sampling_date =
ChemDepth_Union.sampling_date) AND (ChemAlkpH_Info.depth_chemalkph =
ChemDepth_Union.depth_chemalkph) AND (ChemAlkpH_Info.sample_type_name =
ChemDepth_Union.sample_type_name)) ON (ChemCaMgKNa_Info.name_waterbody =
ChemDepth_Union.name_waterbody) AND (C
hemCaMgKNa_Info.station_name = ChemDepth_Union.station_name) AND
(ChemCaMgKNa_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemCaMgKNa_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemCaMgKNa_Info.depth_chemcamgkna = ChemDepth_Union.depth_chemalkph)) ON
(ChemClSO4_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemClSO4_Info.station_name = ChemDepth_Union.station_name) AND
(ChemClSO4_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemClSO4_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemClSO4_Info.depth_chemclso4 = ChemDepth_Union.depth_chemalkph)) ON
(ChemConductivity_Info.name_waterbody = ChemDepth_Union.name_waterbody)
AND
(ChemConductivity_Info.station_name = ChemDepth_Union.station_name) AND
(ChemConductivity_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemConductivity_Info.depth_chemconductivity =
ChemDepth_Union.depth_chemalkph) AND
(ChemConductivity_Info.sample_type_name
= ChemDepth_Union.sample_type_name)) ON (ChemDs
olids_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemDsolids_Info.station_name = ChemDepth_Union.station_name) AND
(ChemDsolids_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemDsolids_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemDsolids_Info.depth_chemdsolids = ChemDepth_Union.depth_chemalkph)) ON
(ChemPhosKjN_Info.name_waterbody = ChemDepth_Union.name_waterbody) AND
(ChemPhosKjN_Info.station_name = ChemDepth_Union.station_name) AND
(ChemPhosKjN_Info.sampling_date = ChemDepth_Union.sampling_date) AND
(ChemPhosKjN_Info.sample_type_name = ChemDepth_Union.sample_type_name) AND
(ChemPhosKjN_Info.depth_chemphoskjn = ChemDepth_Union.depth_chemalkph)
WHERE (((ChemDepth_Union.sampling_date) Between
[Forms]![SelectForQuery1]![startdate] And
[Forms]![SelectForQuery1]![enddate])) AND (ChemDepth_Union.name_waterbody)
IN( 'Blue Lake','Elizabeth Lake);

Okay, backing up to this again. Do me a favor, and after this line:

strSQL = strSQL & strWhere

add

debug.print strSQL

and set a BreakPoint on the line that follows. Run the code. This will
write your entire SQL statement to the Immediate Window. Copy it and
post
it here.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/

 

[Cathleen]

Roger,
Your solution worked. All is working perfectly now. Thank you very much for
all of your help!
Cathleen

 

[Roger Carlson]

You're welcome.

Roger,
Your solution worked. All is working perfectly now. Thank you very much
for
all of your help!
Cathleen