listboxes and query conditions

[Justin Koivisto]

Firstly, I want to thank all that have helped me out with getting grips
on Access each time I've had questions. This has got to be one of the
most helpful groups that I've posted to over the years.

Now my problem...

I have a listbox that I want to use to fill in the where clause of a
query. I've been able to create a string holding the necessary WHERE
clause without many problems. However, I cannot seem to reference the
string inside the query to make it work in Access 2002.

Once the string is built, I set the string to a text box value (which
will be set not to display once I get this figured out):

[Forms]![frmInterestRptSelect]![whereClause].Value = whereClause

Then in Query1, I have this:

SELECT interests.id, interests.name FROM interests WHERE
([Forms]![frmInterestRptSelect]![whereClause])

It pulls up all records as if
[Forms]![frmInterestRptSelect]![whereClause] = "1" or "WHERE" was
removed from the query.

However, if I replace "[Forms]![frmInterestRptSelect]![whereClause]"
with the contents of the text box, the query acts as expected.

I've found some references to using these types of queries by creating
the entire query then setting it as a report recordsource, but what I
need is for this query to be referenced by another query.

What am I missing here, I'm almost convinced it has got to be something
fairly simple...

TIA

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Douglas J. Steele]

Is your listbox multiselect? You can only use the syntax you're showing if
it isn't.

If it is multiselect, take a look at
http://www.mvps.org/access/reports/rpt0005.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:_l***************@news7.onvoy.net...

Firstly, I want to thank all that have helped me out with getting grips
on Access each time I've had questions. This has got to be one of the
most helpful groups that I've posted to over the years.

Now my problem...

I have a listbox that I want to use to fill in the where clause of a
query. I've been able to create a string holding the necessary WHERE
clause without many problems. However, I cannot seem to reference the
string inside the query to make it work in Access 2002.

Once the string is built, I set the string to a text box value (which
will be set not to display once I get this figured out):

[Forms]![frmInterestRptSelect]![whereClause].Value = whereClause

Then in Query1, I have this:

SELECT interests.id, interests.name FROM interests WHERE
([Forms]![frmInterestRptSelect]![whereClause])

It pulls up all records as if
[Forms]![frmInterestRptSelect]![whereClause] = "1" or "WHERE" was
removed from the query.

However, if I replace "[Forms]![frmInterestRptSelect]![whereClause]"
with the contents of the text box, the query acts as expected.

I've found some references to using these types of queries by creating
the entire query then setting it as a report recordsource, but what I
need is for this query to be referenced by another query.

What am I missing here, I'm almost convinced it has got to be something
fairly simple...

TIA

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Justin Koivisto]

Douglas J. Steele wrote:

Is your listbox multiselect? You can only use the syntax you're showing if
it isn't.

If it is multiselect, take a look at
http://www.mvps.org/access/reports/rpt0005.htm at "The Access Web"

Thanks, but I am using ADO... However, I was able to re-arrange the
queries in a way where I could use a WhereCondition when opening a
report instead of trying to use a query to do so...

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com