Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7

    Question Unanswered: Passing a form value to a query

    I am trying to pass a form value to a query as the arguments to an SQL "IN". Based on choices on the form, the form value is set to a comma separated list, i.e. 23,27,29.

    In the query, I set a criteria for a column to:
    IN ([Forms]![MyForm]![MyControl].[Value])

    This WILL work if there is only one value in the control. As soon as I add a comma and a second value: zilch. I know the values are in the db for that column (typing the entire "IN" clause produces what I would expect, as does "SELECT"ing just the form value).

    Anybody know what I am doing wrong?

  2. #2
    Join Date
    Sep 2003
    Posts
    83

    Re: Passing a form value to a query

    Originally posted by dinger
    I am trying to pass a form value to a query as the arguments to an SQL "IN". Based on choices on the form, the form value is set to a comma separated list, i.e. 23,27,29.

    In the query, I set a criteria for a column to:
    IN ([Forms]![MyForm]![MyControl].[Value])

    This WILL work if there is only one value in the control. As soon as I add a comma and a second value: zilch. I know the values are in the db for that column (typing the entire "IN" clause produces what I would expect, as does "SELECT"ing just the form value).

    Anybody know what I am doing wrong?

    May be this will give you an idea,
    lstDrugName is the listbox to select multiple values

    strSQL = "SELECT * FROM <TableName>"

    'create the IN string by looping thru the listbox
    For i = 0 To lstDrugName.ListCount - 1
    If lstDrugName.Selected(i) Then
    If lstDrugName.Column(0, i) = "All" Then
    flgAll = True
    End If
    strIN = strIN & "'" & lstDrugName.Column(0, i) & "',"
    End If
    Next i

    'create the WHERE string, stripping off the last comma of the IN string
    strWhere = " WHERE [DrugID] in (" & Left(strIN, Len(strIN) - 1) & ")"


    Hope it helps

  3. #3
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7
    Thanks, but I'm not having any trouble creating the the IN clause.

  4. #4
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7
    For anyone interested, here is the answer - from a post in microsoft.public.access.queries:


    You cannot do it that way. You can only use a parameter to substitute a
    single literal value in a query. So, if you type in text11:

    "One", "Two", "Three"

    .. the query acts as though you entered:

    WHERE MyField IN ('"One", "Two", "Three"')

    The way to do this correctly is to either use several separate text boxes
    that you reference in the IN clause or to build the predicate dynamically in
    code and use it as a filter for the form or report bound to the query that
    has no IN clause.

    --
    John Viescas, author
    "Microsoft Office Access 2003 Inside Out"
    "Running Microsoft Access 2000"
    "SQL Queries for Mere Mortals"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •