DSUM syntax (1 Viewer)

Ben_Entrew · Dec 16, 2013

Hi all,

I get a syntax error with the following code:

Code:

Public Sub TEST()
DoCmd.RunSQL "Update TEST_TNS Set Total_Net_Sales = "Total_Net_Sales" + DSum("Total_Net_Sales","TEST_TNS","Division = 'Common'") " & _
             " Where Division IN ('MC','AK')"

End Sub

Can anyone help me out here?

Thanks in advance.

Regards,
Ben

Mihail · Dec 16, 2013

How you decided that the DSum syntax is wrong ?
I only can say that the whole SQL is not as should be.
Maybe I have a idea about what is wrong but I think that is better to teach you how to debug.

So, use the following code:

Code:

Public Sub TEST()
Dim strSQL As String
  strSQL = "Update TEST_TNS Set Total_Net_Sales = "Total_Net_Sales" +  DSum("Total_Net_Sales","TEST_TNS","Division = 'Common'") " & _               " Where Division IN ('MC','AK')"
  Debug.Print strSQL
  DoCmd.RunSQL strSQL 
End Sub

When the code stop at error, press CTRL+G in order to see, in the Immediate Window, the SQL string.
What is wrong here?

Ben_Entrew · Dec 16, 2013

Hello Mihail,
thanks for the advice.
But I still can't find the error. The immediate window doesn't show anything.
If the DSUM part is right, then the outer SQL syntax is wrong ?

Regards,
Ben

boblarson · Dec 16, 2013

DoCmd.RunSQL "Update TEST_TNS Set Total_Net_Sales = Nz([Total_Net_Sales],0) + DSum('Total_Net_Sales','TEST_TNS','[Division] ='" & Chr(34) & "Common" & Chr(34) & ") " & _
" Where Division IN ('MC','AK')"

Ben_Entrew · Dec 16, 2013

Thanks for the quick response Bob Larson.
However I still get an error message regarding the syntax.
If I leave the Chr(34) parts out, like:

Code:

 DoCmd.RunSQL "Update TEST_TNS Set Total_Net_Sales = Nz([Total_Net_Sales],0) + DSum('Total_Net_Sales','TEST_TNS',Division = 'COMMON') " & _
              " Where Division IN ('MC','AK')"

it runs through,but doesn't update anything.

My Original table looks like this:

Reporting_Month Customer_Split Total_Net_Sales Quantity Division 102013
3rd party 3132 0 AK 102013
internal other 1234 10 AK 102013 internal other 412342,12 2007 AK 102013 internal other 42523 103 AK 102013 3rd party 100 35480 COMMON 102013 3rd party 1 1189,3
COMMON 102013 internal 1442564,81 240
GT 102013 3rd party 5342 -260 MC 102013 internal 246 448
MC
Basically I want to add the Total_Net_Values of Division COMMON to the Total_Net_Values for Division AK and MC where Customer_Split is also 3rd Party.

Regards,
Ben

Mihail · Dec 16, 2013

Code:

Public Sub TEST()
'UPDATE table     SET newvalue     WHERE criteria; From MSDN (http://msdn.microsoft.com/en-us/library/office/bb221186%28v=office.12%29.aspx)

'Table
Dim strTable As String
    strTable = "TEST_INS"
    
'NewValue
Dim dblOldValue As Double
    dblOldValue = "Total_Net_Sales" '?????? Here should be a numerical value, not a string
Dim dblDSUMresult As Double
    dblDSUMresult = DSum("Total_Net_Sales", "TEST_TNS", "Division = 'Common'")
Dim strNewValue As String
    strNewValue = dblOldValue + dblDSUMresult

'Criteria
Dim strCriteria As String
    strCriteria = "Division IN ('MC','AK')"
    
'Final SQL
Dim strSQL As String
    strSQL = "UPDATE " & strTable & _
             " SET " & strNewValue & _
             " WHERE " & strCriteria
  
'Run the update query
    DoCmd.RunSQL strSQL
End Sub

PS
I saw the Bob's answer but hope that this also will help you

boblarson · Dec 16, 2013

Actually, I goofed. It should be this:

Code:

DoCmd.RunSQL "Update TEST_TNS Set Total_Net_Sales = Nz([Total_Net_Sales],0) + " &  DSum("Total_Net_Sales","TEST_TNS","[Division] =" & Chr(34) & "Common" & Chr(34)) & _
" Where Division IN ('MC','AK')"

Ben_Entrew · Dec 16, 2013

Thanks all to you guys. Now it works out.

Thanks.

Regards,
Ben

Ben_Entrew · Dec 16, 2013

Sorry to bother you again.
Bob Larson maybe you can help me again.
I slightly changed the query and now it doesn't work again

Can I use this update command on a query?
What have I done wrong?
Thanks in advance.

Code:

DoCmd.RunSQL "Update TNS_QUERY Set Total_Net_Sales = " & DSum("Total_Net_Sales", "TNS_QUERY", "[Division] =" & Chr(34) & "Common" & Chr(34)) & _
" Where Division IN ('MC','AK') and Customer_Split = '3rd party'"

DSUM syntax (1 Viewer)

Ben_Entrew

Registered User.

Mihail

Registered User.

Ben_Entrew

Registered User.

boblarson

Smeghead

Ben_Entrew

Registered User.

Mihail

Registered User.

boblarson

Smeghead

Ben_Entrew

Registered User.

Ben_Entrew

Registered User.

Similar threads

Users who are viewing this thread