In clause from form

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

Is there a way to setup a cbo ctrl to be used as an In clause within an SQL
statement?

I set one up

In ([Forms]![frm_rpt_A].[cbo_StatuFltr])

Where my cbo returns value like 'Open' and 'Open, Closed'

but it does work.

How does one go about this?

Thank you in advance,

QB
 
I need to pay a little more attention when I type:

but it does work.
should have been
but it does not work.
 
You will need some code to get this to work with IN (). I generally have code
that would modify the SQL property of a saved query. If the query is the
record source of a form or report, you can use code to build a where
condition for use in the DoCmd.Open... method.
 
QB said:
Is there a way to setup a cbo ctrl to be used as an In clause within an SQL
statement?

I set one up

In ([Forms]![frm_rpt_A].[cbo_StatuFltr])

Where my cbo returns value like 'Open' and 'Open, Closed'

but it does work.
Click to expand...


No, there is no way to use a single parameter as a list in
an IN operator (even if you put the needed quotes around
each string value).

OTOH, you can use VBA code to construct the entire query's
SQL statement so you do not need to use a parameter.

Another way for a simple list like your example, you can use
a parameter query with this kind of Where clause condition:

WHERE "," & Forms!frm_rpt_A.cbo_StatuFltr & "," Like "*," &
thefield & ",*"

but it may be unacceptably slow.
 
In a query, you can do this in the where clause. This is not the most
efficient method (slow with large numbers of records). It is also subject to
problems if your data can contain values that appear in other values. For
instance, field can contain values Abbot, Abbey, Ab, Bey and you look for Bey
you are also going to get matches on Abbey

WHERE Instr([Forms]![frm_rpt_A].[cbo_StatuFltr],[YourField]) > 0

IF you are building that in query design view
Field: Instr([Forms]![frm_rpt_A].[cbo_StatuFltr],[YourTable].[YourField])
Table: blank
Show: Not checked
Criteria: >0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Marshall said:
QB said:
Is there a way to setup a cbo ctrl to be used as an In clause within an SQL
statement?

I set one up

In ([Forms]![frm_rpt_A].[cbo_StatuFltr])

Where my cbo returns value like 'Open' and 'Open, Closed'

but it does work.
Click to expand...


No, there is no way to use a single parameter as a list in
an IN operator (even if you put the needed quotes around
each string value).

OTOH, you can use VBA code to construct the entire query's
SQL statement so you do not need to use a parameter.

Another way for a simple list like your example, you can use
a parameter query with this kind of Where clause condition:

WHERE "," & Forms!frm_rpt_A.cbo_StatuFltr & "," Like "*," &
thefield & ",*"

but it may be unacceptably slow.
Click to expand...
 
Back
Top