Replace values by DSUM

[Ben_Entrew]

Hi all,

I'm not sure if this is possible. I want to replace some values in a TNS_QUERY with DSUM.
The error message tells me I have to use an updateable table.
How can I avoid to create a query and replace the values directly in the original table TNS? I just group on the original table.

Thanks in advance.

Regards,
Ben

Public Sub TNS_QUERY()

Dim strSQL As String
Dim x As Double
Dim qdf As QueryDef
strSQL = "SELECT TNS.[Division],TNS.[Customer_Split],SUM([TOTAL_NET_SALES]) as [Total_Net_Sales_in_EUR],TNS.[Reporting_Month] " & _
         " FROM TNS " & _
         " WHERE [Total_Net_Sales] <> 0 AND [Reporting_Month] = '" & repmonth & "' " & _
         " GROUP BY TNS.[Division],TNS [Customer_Split],TNS.[Reporting_Month]"
    
    With CurrentDb
                
                Set qdf = CurrentDb.CreateQueryDef("TNS_QUERY", strSQL)
                .Close
    End With
 DoCmd.RunSQL "Update TNS_QUERY Set Total_Net_Sales_in_EUR = Nz([Total_Net_Sales_in_EUR],0) + DSum('Total_Net_Sales_in_EUR','TNS_QUERY',Division = 'COMMON') " & _
              " Where Division IN ('MC','AK')"

 

[TJPoorman]

You can't update a GROUP BY query. You would need to run the update query on the table directly, not the query.

 

[CJ_London]

you cant update a query (or append or delete for that matter) as you are trying to do - end of story.

You need to turn your tns qery into a make table query which makes a table you can call TNS_Table then modify your update query to work off that table instead.

Note that domain functions are not good to use in queries, unless you only have a few rows and by the looks of it, you can modify your tns_query to include what you want to update anyway.

Also, if you are going to use dsum, you have missing quotation marks in the criteria