Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    Unanswered: Help with Union Query and Adding (All) to a dropdown

    I use the following sql statement as the recordsource of a combo box in order to add "(All)" to a combo box
    it works fine since the combo box only shows 1 column

    SELECT Distinct Department FROM qryLookup_PODepartments UNION SELECT '(All)' FROM qryLookup_PODepartments;

    I have a different issue where I want the combo box to show 3 columns
    I am doing this because I am setting the recordsource of the dropdown to match a different dropdown
    first a user selects a department from a dropdown - so I want the Accounts for only that dept to show

    How can I create a sql statement that will do this, and show "(All)" as well... when I use the code below - I get an error saying the number of fields do not match in the union query...

    SELECT DISTINCT tblBudgetDetail.GLNumber, tblBudgetDetail.Account, tblBudgetDetail.Department FROM tblBudgetDetail WHERE (((tblBudgetDetail.Department) Like [Forms]![frmPOListing_ByAccount]![txtDepartment])) UNION Select '(All)' from tblBudgetDetail
    ORDER BY tblBudgetDetail.GLNumber;

    Any help is appreciated..


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    all SELECTs in a union have to have the same number of columns

    so just add some NULL placeholders
    SELECT GLNumber
         , Account
         , Department 
      FROM tblBudgetDetail 
     WHERE Department Like [Forms]![frmPOListing_ByAccount]![txtDepartment] 
    SELECT '(All)' 
         , NULL
         , NULL 
      FROM tblBudgetDetail
        BY GLNumber
    note that the 2nd SELECT returns as many '(All)' rows as there are rows in the tblBudgetDetail table, and then the UNION removes all the dupes -- a rather expensive way of inserting a single row into the result set

    better would be to select '(All)' from a single-row table (if you have one) | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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