asked on

Syntax Error on Update Query

Trying to use SQL in Access update values from one table to another
I want to update the values in table xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation from the values in tblTaxRecs if the BRT, TaxYear and TaxHeaderID are equal in both tables
Here's what I have, which isn't working

updateString = " Update xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation T1 " & _
                " Set [PostPrincipalAmt] = T2.[PrincipalAmt] " & _
                "  ,  [PostPenaltyAmt]   = T2.[PenaltyAmt]   " & _
                "  ,  [PostInterestAmt]  = T2.[InterestAmt]  " & _
                "  ,  [PostLienCost]     = T2.[LienCost]     " & _
                "  ,  [PostAttyFeesAmt]  = T2.[AttyFeesAmt]  " & _
                "  ,  [PostActive_YN]    = T2.[Active_YN]    " & _
                " FROM tblTaxRecs T2                         " & _
                " Where T1.[TaxYear]     = T2.[TaxYear]      " & _
                "  AND  T1.[BRT]         = T2.[BRT]          " & _
                "  AND  T1.[TaxHeaderID] = T2.[TaxHeaderID]  "

DoCmd.RunSQL updateString

Open in new window

The error I'm getting is

Syntax error (missing operand) in query expression T2.[Active_YN] from tblTaxRecs T2

Can anyone spot the error?

Éric Moreau

not sure for Access but with MS SQL Server, the statement would be:

updateString = " Update  T1 " & _
                " Set [PostPrincipalAmt] = T2.[PrincipalAmt] " & _
                "  ,  [PostPenaltyAmt]   = T2.[PenaltyAmt]   " & _
                "  ,  [PostInterestAmt]  = T2.[InterestAmt]  " & _
                "  ,  [PostLienCost]     = T2.[LienCost]     " & _
                "  ,  [PostAttyFeesAmt]  = T2.[AttyFeesAmt]  " & _
                "  ,  [PostActive_YN]    = T2.[Active_YN]    " & _
                " FROM xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation as T1 inner join tblTaxRecs T2                         " & _
                " ON T1.[TaxYear]     = T2.[TaxYear]      " & _
                "  AND  T1.[BRT]         = T2.[BRT]          " & _
                "  AND  T1.[TaxHeaderID] = T2.[TaxHeaderID]  "

DoCmd.RunSQL updateString

Open in new window

mlcktmguy

ASKER

Thank you Eric but that's throwing the same error.

Éric Moreau

can you paste your new query?

Fabrice Lambert

Hi,
With MS Access, you need to use the "AS" keyword to declare an alias:

updateString = " Update xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation AS T1 " & _
                " Set [PostPrincipalAmt] = T2.[PrincipalAmt] " & _
                "  ,  [PostPenaltyAmt]   = T2.[PenaltyAmt]   " & _
                "  ,  [PostInterestAmt]  = T2.[InterestAmt]  " & _
                "  ,  [PostLienCost]     = T2.[LienCost]     " & _
                "  ,  [PostAttyFeesAmt]  = T2.[AttyFeesAmt]  " & _
                "  ,  [PostActive_YN]    = T2.[Active_YN]    " & _
                " FROM tblTaxRecs AS T2                         " & _
                " Where T1.[TaxYear]     = T2.[TaxYear]      " & _
                "  AND  T1.[BRT]         = T2.[BRT]          " & _
                "  AND  T1.[TaxHeaderID] = T2.[TaxHeaderID]  "

DoCmd.RunSQL updateString

Open in new window

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks you,