Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    23

    Unanswered: UNION Query Question

    I have some list boxes that I use in a QBF form. I need the list to look like:

    !All
    John Smith
    Michael McGee
    Robert James
    etc...

    The query below only returns the first 4 characters of the user name (the length of "!ALL")

    !All
    John
    Mich
    Robe

    Is this typical? I tried to find something about it in the manual...

    Thanks,

    Rick

    SELECT DISTINCT "!All" AS Name, 0 AS UserID FROM tblUser;
    UNION SELECT CONCAT(tblUser.FirstName, " ", tblUser.LastName) AS Name, tblUser.UserID
    FROM tblUser
    ORDER BY Name;
    A good cook doesn't use a smoke alarm for a timer.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is most typical

    the datatype specs for the result set are taken from the first query in the union

    just reverse the order --
    Code:
    SELECT CONCAT_WS(' ',tblUser.FirstName, tblUser.LastName) AS Name
         , tblUser.UserID 
      FROM tblUser
    UNION ALL
    SELECT DISTINCT '!All'
         , 0 
      FROM tblUser
    ORDER BY Name
    note: use CONCAT_WS so that you don't get a leading space when the first name is null

    use UNION ALL to avoid a complete sort of the result set looking for duplicate rows (because there can't be any)

    rudy
    http://r937.com/

  3. #3
    Join Date
    Aug 2002
    Posts
    23
    Thanks for the reply... I didn't think about reversing the order.
    A good cook doesn't use a smoke alarm for a timer.

Posting Permissions

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