Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2015
    Posts
    2

    Unhappy Unanswered: Whats wrong with this SQL Union query?

    Hi,

    I am trying to follow this example of adding "All" to my dropdown box

    http://access.mvps.org/access/forms/frm0043.htm

    I got this code put together following the tutorial

    SELECT DISTINCTROW qry_Fld_Res.ReservoirCode, qry_Fld_Res.ReservoirName, qry_Fld_Res.FieldCode

    FROM qry_Fld_Res

    UNION

    Select Null as AllChoice , "(All)" as Bogus

    FROM qry_Fld_Res

    ORDER BY qry_Fld_Res.ReservoirName;



    But something is wrong with it it does not work

    Can someone tell me what I am doing wrong?

    Thank you

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Two things:
    • All SELECT clauses must have the same number of fields - you have three in the top and two in the bottom
    • All SELECT clauses must have the same field names or aliases
    Also, given that the first SELECT clause determines the output field names, you don't needs them in the subsequent SELECT clauses. Try this:
    Code:
    SELECT DISTINCTROW
      qry_Fld_Res.ReservoirCode
    , qry_Fld_Res.ReservoirName
    , qry_Fld_Res.FieldCode
    FROM
      qry_Fld_Res
    UNION
    SELECT
      Null
    , '(All)'
    , Null
    FROM
      qry_Fld_Res
    ORDER BY
      ReservoirName;
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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