Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: UNION ALL SYNTAX Error (Missing Operator) in Query Expression

    I'm trying to convert my Table Columns/Fields into Rows using the UNION ALL clause in my SQL. I've tried making different corrections, but keep getting the Syntax Error " (missing operator) in query expression '12XF1652GA' ". I would very much appreciate someone looking at the following SQL statement below and tell me what I am doing wrong. Thx -Tim

    SELECT SGLAcct, ‘12XF1652GA’ AS Fund, 12XF1652GA AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, ‘12XF1652MN’ AS Fund, 12XF1652MN AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, ‘12XF1652ST’ AS Fund, 12XF1652ST AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, ‘XXXF1652GA’ AS Fund, XXXF1652GA AS Amount, [Begin/End]
    FROM [0TBL-BrioBB];

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Tim,

    Try [12F1652GA]

    Wayne

  3. #3
    Join Date
    Sep 2012
    Posts
    21
    Hey Wayne,

    Yeah, I've already tried that and it treats that field as if its a paramater value (requesting that I enter a value). I appreciate your input and am open for any other ideas. Thx -Tim

  4. #4
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Tim,

    If it treats it like a parameter, then it must not be a valid field name.

    All queries have the same number of columns and datatypes.
    It must be an invalid field name reference,

    Wayne

  5. #5
    Join Date
    Sep 2012
    Posts
    21
    Hello again Wayne,

    In case it helps, please see my attachment to view my table in Datasheet and Design View. Thank you for staying with me.

    -Tim
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If 12f16.... is the name of a column then it should not be delimited with quotes marks.
    Use [] to delimit column names which use reserved words ir symbols
    As waynecal states if you are being asked fir a oarameter value then you have a tyoo. Effectively its saying it cannit find that column in the specified table. So check the column does exist in the table, check for typos
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2012
    Posts
    21
    I finally figured out my syntax errors. Wayne's previous suggestion of using brackets around my Amount field names wouldn't work because I needed to use double quotes instead of single quotes around my Fund Name. Also, field names that begin with a numeric character won’t work without brackets. Field names that begin with an alpha character will work without brackets.
    So here is the solution that now works:

    SELECT SGLAcct, "12XF1652GA" AS Fund, [12XF1652GA] AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, "12XF1652MN" AS Fund, [12XF1652MN] AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, "12XF1652ST" AS Fund, [12XF1652ST] AS Amount, [Begin/End]
    FROM [0TBL-BrioBB]
    UNION ALL
    SELECT SGLAcct, "XXXF1652GA" AS Fund, [XXXF1652GA] AS Amount, [Begin/End]
    FROM [0TBL-BrioBB];

  8. #8
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Tim,

    Glad it's working.

    The 2nd column in your Select can be delimited with single-quotes.

    Wayne

Posting Permissions

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