Re: Multiselect Listbox as qualifier for query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 10/25/04 

Date: Mon, 25 Oct 2004 23:13:30 +0800

That's correct, Mike. You cannot just pass the entire IN expression as a
parameter to the query. You can pass that kind of string in the
WhereCondition of the OpenReport action though. Or you can re-write the SQL
statement as you suggest. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike Blanchard" <MikeBlanchard@discussions.microsoft.com> wrote in message
news:AB62B002-7B67-4E6E-8276-7C13C91582B9@microsoft.com...
> Thanks for the replies, gentlemen.
>
> Tried passing the string in the format 'IN("FLA", "GA")' to the report but
> it didn't seem to work.  To do so, I set the CRITERIA line for the field 
> to
> point the variable where I had the text loaded in the form.  I looked at 
> the
> SQL that generated and it place an "=" sign in between the field name and 
> the
> clause which would result in the SQL expression...
>
> GROUP = IN("FLA", "GA") which was unsuccessful.
>
> We'll try the site you've suggested.  I was hoping to be able to use the
> variable approach as we are trying to link a standard report filtering 
> screen
> that is presented to the user and will generate 3 qualifiers and then have
> other qualifiers within the individual query definitions.
>
> This approach seems to lean toward a need to artificially generate the
> entire where clause and pass it as an argument as opposed to the "half
> artificial with the remainder through the standard query design window"
> approach we were hoping for.   Appreciate the help nonetheless.
>
> Mike
>
> "Allen Browne" wrote:
>
>> If you need an example of how to code Tom's first suggestion, see:
>>     Use a multi-select list box to filter a report
>> at:
>>     http://members.iinet.net.au/~allenbrowne/ser-50.html
>>
>>
>> "Tom Ellison" <tellison@jcdoyle.com> wrote in message
>> news:no2jn011c1um9qtpaq3sig4bke563e5ur2@4ax.com...
>> > Dear Mike:
>> >
>> > There are two approaches to make this work using a listbox with
>> > multiple selections.  One is to code the building of the SQL
>> > dynamically so it generates the string
>> >
>> >  IN ("FLA", "GA")
>> >
>> > The other is to have a local table into which you place the selected
>> > state abbreviations and then join to that table.
>> >
>> > If you want the users to enter a list as a query parameter, the
>> > requirements on the user will be quite stringent compared with the
>> > list box.  Obviously, the user would have to spell each state
>> > abbreviation exactly and never omit a comma.  In addition, you must
>> > either have them always put a space between items, after the comma, or
>> > never do so.
>> >
>> > You could then use INSTR() to search the string.  To do this at all
>> > reliably, assuming the users do not put spaces after commas, the
>> > expression could be:
>> >
>> > InStr("," & [Enter States to filter separated by commas] & ",", "," &
>> > StateColumn & ",") <> 0
>> >
>> > This would search for
>> >
>> > ,FLA,
>> >
>> > in the string of entered states
>> >
>> > ,FLA,GA,
>> >
>> > This puts quite a burden on the user, and so is inferior to using the
>> > list box.
>> >
>> > Tom Ellison
>> > Microsoft Access MVP
>> > Ellison Enterprises - Your One Stop IT Experts
>> >
>> >
>> > On Fri, 22 Oct 2004 14:25:02 -0700, "Mike Blanchard"
>> > <MikeBlanchard@discussions.microsoft.com> wrote:
>> >
>> >>In Access 97, I'm trying to use the IN clause as a qualifier in a
>> >>parameter
>> >>query so the user can type in more than one value to filter with on a
>> >>single
>> >>field
>> >>
>> >>Hardcoding the criteria to the following works fine...
>> >>IN ("FLA", "GA")
>> >>
>> >>I can't get the following to work - any advice?
>> >>IN ([ENTER STATES TO FILTER ON SEPARATED BY COMMAS])
>> >>
>> >>The parameter box displays but no matter how I enter the data (quotes; 
>> >>no
>> >>quotes; single quotes; double quotes, etc), nothing qualifies if I 
>> >>enter
>> >>more
>> >>than 1 state.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Query to Filter by Dates, Using Form
    ... This is the SQL that works: ... Dim strDateCondition As String ... drop the stored query and create a new one with the SQL you have built? ...
    (microsoft.public.access.queries)
  • Re: Requery subform
    ... are just changing the SQL? ... remove this function and relevent code because the query always exists. ... Dim strProjectType As String ... Dim strProjectStatus As String ...
    (comp.databases.ms-access)
  • Re: Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
    ... Number of characters in a cell in the query design grid 1,024 ... Since the SQL parser mentions an error in the FROM clause, ... The only problem I can see is the SQL string is very long. ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)