Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Select Query - Criteria

    I'm attempting to place criteria in a select query from a table. If I was hardcoding the criteria directly it would look like:

    In ('A', 'B', 'C')

    and return all values equal to A, B, and C. I need to do the exact same thing by referencing a table that's bound to a combo box. I can of course pull in a single value like 'A' and return all values = 'A' but having "In ('A', 'B', 'C')" in the table doesn't work.

    Any suggestions would certainly be appreciated.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    I can't quite get my head round what you are trying to do here. Are you saying you want a combo to have an option of selecting "in('A','B','C') and that selection will be used in a query (presumably in vba cretaed select statement) ?

    I don't understand the point about the combo being bound to a table. Do you really mean the combo is using a table as its row source and "in('A','B','C') is one of the values in this table ?

    Chris

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think it could be I want to select letters from a combo box, and use those in my recordsource

    eg if I select A,D,H then I want to return all rows with A, D or H

    but you could be right.......
    ...who knows....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for looking at this.

    Sorry, that could have been more clear. The combo box is using a table as its row source and "in('A','B','C') is one of the values in the table. The user only sees a descripter on the pull-down, but the bound column is sourced to the table and could be 'A' or 'B' or 'C' or ('A','B') etc.

    How do I reference the value ('A','B','C') from my staging table in the criteria of my select query with expression builder?

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I assume you are using the query builder. I don’t think you can do what you want like that. The problem is however you try to reference the criteria, query builder will keep treating it as a string.

    I can think of a few ways of doing this. Probably the quickest (but maybe not the most efficient) is to write some vba and use the CASE statement to select and run the appropriate SQL. Are you familiar with vba ?

    Another way using vba is to have a column in your table which has the sql pre-input (or just the last bit of the sql) depending on the item selected in the combo (I think you already have this). Then you can have a lookup in vba to select the appropriate sql or partial sql and run and appropriate sql statement based on it.

    A completely different approach that doesn’t use vba is to present your table as a subform on your form so that users can tick (in a new column) which values e.g. just tick A and B. You then have run a pre-written query that joins your ABC table with your main data with the criteria on only ticked options. Since you’ve only got A,B and C this might seem long winded as you only need 6 options in your combo, however, if you decide to go to A,B,C,D then the combo is going to need 24 options whereas this method still only presents the user with 4 tick options – much neater I think.

    If you’re always only going to have just ABC then I’d be inclined to hard code three unbound check boxes on your form and use vba to take the values and build the appropriate sql. But always is never always !

    What were you planning to use the query for ?
    - basis for a report ?
    - listing data in your form ?

    Hth
    Chris

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    The select query is the data source for a number of reports. Unfortunately, my knowledge of VBA is limited so I'll need to figure out another work-around. Again, I appreciate your suggestions.

Posting Permissions

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