Results 1 to 4 of 4
  1. #1
    khackbarth is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    2

    how to put multiple integers in a parameter

    I would like to create a sql statement that looks something like:



    select *
    from myTable
    where myTable.ItemID in ([Parameter])

    Actually, I'm passing these values to a stored procedure using Visual Basic but I can't even get this to work from the Access Query Designer.

    I want to be able to supply a list of IDs (i.e., long integers) but Access treats the parameter as having a single value contained in it. Comma separation doesn't do anything.

    I'd like to avoid using string parsing functions like Instr() and just take advantage of the "in" operator.

    Am I doing something wrong or is this impossible.

    Thanks,
    Ken

  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Try

    WHERE TABLE.FIELD In(15,23,35,44,5667)

  3. #3
    khackbarth is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    2

    hard coding the values works fine

    The problem is in using a parameter to provide the values in parentheses. The more I read about this, it looks like it just can't be done in Access 2003. In fact the microsoft links that I've visited, just describe either using Instr([parameter with comma separated values],Table.column) > 0 as the answer or building a database function to do the work.

    The Instr() approach works but isn't robust since 21 matches both ,21, and ,321,

    Thanks anyway.

  4. #4
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Don't give up on Access. There are many ways in Access to accomplish something.

    Building SQL queries is fundamental and very, very important that a user understands how to doe this.

    for your issue, you need to learn how to dynamically build a SQL string and SQL criteria, then plugging same in a form's record source and requering or stuffing it in the form's filter property and setting the form's filteron property.

    I suggest that you get a SQL hornbook and work through the elementary examples.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-10-2009, 10:33 PM
  2. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  3. Initial Parameter Value?
    By justgeig in forum Queries
    Replies: 7
    Last Post: 12-08-2009, 05:18 PM
  4. Need help with parameter quey
    By toptech in forum Queries
    Replies: 0
    Last Post: 10-22-2009, 05:20 PM
  5. Can you use a parameter in findrecord?
    By P5C768 in forum Programming
    Replies: 2
    Last Post: 08-20-2009, 04:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums