Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ALL as choice in Combo Box.

    I have set up a statement:

    SELECT DISTINCT [Locations].[Region] FROM Locations UNION select null as AllChoice, "(ALL)" as bogus from locations ORDER BY region;

    in an attempt to add the choice of "ALL" in a combo that generates a report for me. I am getting an error:

    'The number of columns in the two selected tables do not match'

    Any help?
    There is always a way.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL as choice in Combo Box.

    I believe you need to select 2 columns from your Location table in order to form the Union. Also, it is not clear to me why you Select Null. Nonetheless, here is the code I use for an All Regions choice in a combo box:

    SELECT DISTINCTROW Region.[Region Code], Region.[Region Name] FROM Region UNION SELECT "*", "All Regions" FROM Region;

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL as choice in Combo Box.

    The situation is there are 6 regions and 233 store names. If I select more than one field from the locations table the SELECT DISTINCT displays too many choices.

    The selection in this combo becomes the criteria in the region line of the query. If no distinct region is selected the criteria should be null.

    The "ALL" selection in your suggestion does not yield the correct result... the report is blank as it does not recognize that ALL selected should be a null in the query criteria line.

    I am still trying...
    There is always a way.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ALL as choice in Combo Box.

    Instead of trying to make the criteria line Null (I assume you mean blank instead of Is Null, right?), use the approach previously suggested but put a Like operator in your criteria line. The expression "Like '*'" (without the outter quotes) returns any value for that field, including nulls.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL as choice in Combo Box.

    Please forgive my beginnerness... where would you put the LIKE statement in the expression above?

    I am learning that usign Access at this level is like trying to learn another language. "I am in China trying to order a pizza and everyone is showing me where the bathroom is"... I will get there...
    There is always a way.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ALL as choice in Combo Box.

    I didn't phrase it well. What I meant was that the selection of the All item in the combobox should return an asterisk. Then in the recordsource of the report, use the like operator. I would set the combobox source to something like this:
    <hr>SELECT DISTINCTROW Region.[Region Code], 2 AS SortKey FROM Region UNION SELECT "*" As [Region Code], 1 As SortKey FROM Region OrderBy SortKey, [Region Code]<hr>
    Then, if you base the report on the combobox contents, the SQL should read something like this (I assumed you were using a table named Stores and that your selection from was called MyForm):
    <hr>SELECT * FROM Stores WHERE Stores.[Region Code] Like Forms!MyForm!cboRegion<hr>
    Charlotte

Posting Permissions

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