dcsimg
Results 1 to 9 of 9

Thread: MS Access Error - Syntax Error in Union Query

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    MS Access Error - Syntax Error in Union Query

    Hi, all. I'm trying to create the following Union query in MS Access and am getting an error: Syntax Error in Union Query. I just don't see what I'm doing wrong. Can anyone help? I'm assuming I'm getting this error due to the use of reserved words, but I thought the brackets would take care of that. It's not. Thanks for any help you can give.


    SELECT qryWells_CurrDay_Master.[FormattedDate],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Value Date],
    [qryWells_CurrDay_Master.tblWFBCurrDayTran.[Tran Desc],
    qryWells_CurrDay_Master.tblWFBCurrDayBal.[Opening Avl Bal],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Acct No],
    qryWells_CurrDay_MastertblWFBCurrDayTran.[As-Of Date],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[As-Of-Time],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Bank Name],
    qryWells_CurrDay_Master.[Field0],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Acct Name],
    qryWells_CurrDay_Master.[BAI Type Code],
    qryWells_CurrDay_Master.[Field1],
    qryWells_CurrDay_Master.[Debit Amt],
    qryWells_CurrDay_Master.[Credit Amt],
    qryWells_CurrDay_Master.[0 Day Flt Amt],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[1 Day Flt Amt],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[2+ Day Flt Amt],
    qryWells_CurrDay_Master.[Customer Ref No],
    qryWells_CurrDay_Master.[[Tran Status],
    qryWells_CurrDay_Master.[Descriptive Text 1],
    qryWells_CurrDay_Master.[Descriptive Text 2],
    qryWells_CurrDay_Master.[Descriptive Text 4],
    qryWells_CurrDay_Master.[Description],
    qryWells_CurrDay_Master.[Opening Ledger Bal],
    qryWells_CurrDay_Master.[Field3],
    qryWells_CurrDay_Master.[Curr Ledger Bal],
    qryWells_CurrDay_Master.[Curr Avl Bal],
    qryWells_CurrDay_Master.[Chg in Avl Bal Since Opening]


    FROM qryWells_CurrDay_Master LEFT JOIN qryWells_IntradayMaster_MemopostInput ON qryWells_CurrDay_Master.[FormattedDate] = qryWells_IntradayMaster_MemopostInput.[Date];

    UNION ALL

    SELECT qryWells_IntradayMaster_MemopostInput.[Date],
    qryWells_IntradayMaster_MemopostInput.[Assoc Name],
    Null As tblWFBCurrDayTran.[Value Date],
    qryWells_IntradayMaster_MemopostInput.[Description],
    Null As tblWFBCurrDayBal.[Opening Avl Bal],
    Null As tblWFBCurrDayTran.[Acct No],
    Null As [tblWFBCurrDayTran.As-Of Date],
    Null As[ tblWFBCurrDayTran.As-Of-Time],
    Null As tblWFBCurrDayTran.[Bank Name),
    Null As [Field0],
    Null As tblWFBCurrDayTran.[Acct Name],
    Null As [BAI Type Code],
    Null as [Field1],
    qryWells_IntradayMaster_MemopostInput.[Credit/Debit],
    qryWells_IntradayMaster_MemopostInput.[Credit/Debit],
    Null As [0 Day Flt Amt],
    Null As tblWFBCurrDayTran.[1 Day Flt Amt],
    Null As tblWFBCurrDayTran.[2+ Day Flt Amt],
    Null As [Customer Ref No],
    Null As [Tran Status],
    qryWells_IntradayMaster_MemopostInput.[Description],
    Null As [Descriptive Text 2],
    qryWells_IntradayMaster_MemopostInput.[Add'l Description],
    Null As [Description],
    Null As [Opening Ledger Bal],
    Null As [Field3],
    Null [Curr Ledger Bal],
    Null As [Curr Avl Bal],
    Null As [Chg in Avl Bal Since Opening]

    FROM qryWells_IntradayMaster_MemopostInput RIGHT JOIN qryWells_CurrDay_Master ON qryWells_IntradayMaster_MemopostInput.[Date] = qryWells_CurrDay_Master.[FormattedDate]

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,415

    Re: MS Access Error - Syntax Error in Union Query

    Welcome to VBForums

    The issue will be at least partly caused by the last character before the word Union.... you have a semi-colon there, which is used to indicate the end of the SQL statement (but you explicitly want the SQL statement to be continued into the Union).

    The semi-colon isn't actually needed anyway, so simply remove it.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

    Re: MS Access Error - Syntax Error in Union Query

    You have at lest two spurious brackets as well. I stopped looking after two.
    Code:
    SELECT qryWells_CurrDay_Master.[FormattedDate], 
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Value Date], 
    [qryWells_CurrDay_Master.tblWFBCurrDayTran.[Tran Desc], 
    qryWells_CurrDay_Master.tblWFBCurrDayBal.[Opening Avl Bal], 
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Acct No], 
    qryWells_CurrDay_MastertblWFBCurrDayTran.[As-Of Date], 
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[As-Of-Time], 
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Bank Name], 
    qryWells_CurrDay_Master.[Field0], 
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[Acct Name],
    qryWells_CurrDay_Master.[BAI Type Code], 
    qryWells_CurrDay_Master.[Field1],
    qryWells_CurrDay_Master.[Debit Amt],
    qryWells_CurrDay_Master.[Credit Amt],
    qryWells_CurrDay_Master.[0 Day Flt Amt],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[1 Day Flt Amt],
    qryWells_CurrDay_Master.tblWFBCurrDayTran.[2+ Day Flt Amt],
    qryWells_CurrDay_Master.[Customer Ref No],
    qryWells_CurrDay_Master.[[Tran Status],
    qryWells_CurrDay_Master.[Descriptive Text 1],
    qryWells_CurrDay_Master.[Descriptive Text 2],
    qryWells_CurrDay_Master.[Descriptive Text 4],
    qryWells_CurrDay_Master.[Description],
    qryWells_CurrDay_Master.[Opening Ledger Bal],
    qryWells_CurrDay_Master.[Field3],
    qryWells_CurrDay_Master.[Curr Ledger Bal],
    qryWells_CurrDay_Master.[Curr Avl Bal],
    qryWells_CurrDay_Master.[Chg in Avl Bal Since Opening]
    
    
    FROM qryWells_CurrDay_Master LEFT JOIN qryWells_IntradayMaster_MemopostInput ON qryWells_CurrDay_Master.[FormattedDate] = qryWells_IntradayMaster_MemopostInput.[Date]
    There also appears to be a missing dot in there.

    You really ought to learn how to use aliases as well. Doing so would make your code significantly more readable.

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,396

    Re: MS Access Error - Syntax Error in Union Query

    Be careful saying semi-colon is not really needed. Yes in this case the ; before the UNION statement is wrong. Some database systems require the ; be in place to execute a SQL statement. Just because MS don't require it right now do not mean they are unnecessary. In some cases MS actually will enforce that it is in place even now.

    And there is no guarantee that they will not require it in the future for all statements (to ensure ISO requirements)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: MS Access Error - Syntax Error in Union Query

    Thanks to all for the help. I've taken out the ";" in the middle of the statement, but am still getting an "Invalid Argument" error. Is there anything else you can see that is incorrect in my statement?

    Thanks much,
    Penny

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,415

    Re: MS Access Error - Syntax Error in Union Query

    Post #3 has useful info... the red parts of that post are bits that you have got wrong (extra brackets, and a missing dot), so it would be useful to change those, and check for any other typos.

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: MS Access Error - Syntax Error in Union Query

    Quote Originally Posted by si_the_geek View Post
    Post #3 has useful info... the red parts of that post are bits that you have got wrong (extra brackets, and a missing dot), so it would be useful to change those, and check for any other typos.
    Thank, you, Si. I should've mentioned in my post earlier today that I've gone through it since the first post and corrected any erroneous information. I didn't want to re-post it since it's so big, but it may be helpful to do so.

  8. #8
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    1,006

    Re: MS Access Error - Syntax Error in Union Query

    Quote Originally Posted by PennyPinscher View Post
    Thank, you, Si. I should've mentioned in my post earlier today that I've gone through it since the first post and corrected any erroneous information. I didn't want to re-post it since it's so big, but it may be helpful to do so.
    Basically at this point you are asking us to help you fix something that we haven't seen (your updated query). So, yeah, you need to post what you have now.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

    Re: MS Access Error - Syntax Error in Union Query

    Quote Originally Posted by PennyPinscher View Post
    I didn't want to re-post it since it's so big
    And part of the reason for that is that you're not using aliases, which I've already suggested you should be. Not using aliases makes your code harder to read and therefore easier to make errors and harder to find them. You should also avoid using table and column names with spaces in them, which also makes the code harder to read.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width