Re: Multiselect listbox parameter query followup

Hi,


The easier way would be to create the WHOLE query as a string, form the
SELECT up to the WHERE clause your code produces. With that single string,
representing a valid SQL statement, you can use it as record source (form,
sub-form, report), or as row source (list box, combo box) as if it was a
saved query. Just be sure the string is a valid SQL statement: Debug.Print
it in the immediate debug window, then, paste it in the SQL view of a new
Query. That new query should then run (to see data in data view) without
producing any error.


Hoping it may help,
Vanderghast, Access MVP


"Dave Twombly" <dtwombly@xxxxxxxxx> wrote in message
news:1149103122.181306.292220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm trying to make a multiselect listbox provide one or more parameters
for a query. I've found useful code for concatenating the items
selected in the list box at
http://www.mvps.org/access/forms/frm0007.htm, but I don't know how to
make my query read that string as its parameter. The website says this:
"Note: You can still use a parameterized query provided you pass the
entire Where clause to it via code as a parameter. (eg. Have the query
reference a hidden control to which you manually assign the complete
WHERE clause using the following logic...[Code begins])"

What I don't know how to do here is assign the WHERE clause that the
code produces to a control on my form. I assume that once that's done I
simply refer the query condition to the control, much as if it were a
single-select listbox.

Would someone please help me complete these final steps of getting the
query to accept the string as one of its parameters?

(Incidentally, I found a helpful way to do this for reports on
http://allenbrowne.com/ser-50.html, but I can't make that method work
for a query.)

Thanks,
Dave



.

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: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: strSQL using Like "*"
    ... so you need to craft the WHERE clause so that it does not compare to a field at all. ... Switch the query to SQL View, and edit the WHERE clause so it looks like this: ... A much more efficient solution is to create the filter string dynamically, in code, from the boxes where the user entered a value. ... to use a strSQL for the recordset and what I am querying on is based on 5 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problems in Access (error 2185) creating custom form to build dynamic query.
    ... intCkPriority = Me.ckPriority.value ... > on each and every control in code just to copy the text or value into the ... > I queried for "query by form" but so far, have not found what I am ... >> use in the WHERE clause of the SQL string. ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... 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)