syntax error on sum in sql using Excel VBA

[Conrado Natac]

I get a syntax error on the sum code, how do I use the sum in sql

I want to use the sum to total the amount per invoice

'here is my complete code


Option Explicit

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Public Sub OpenDB()


If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name


cnn.Open
End Sub

Public Sub closeRS()
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
End Sub


'here is the error

Private Sub cmdbalance_Click()
'this gets the balance of an invoice, after payments
' per one customer


Sheets("View").Visible = True
Sheets("View").Select
Range("A30:N40000").ClearContents

closeRS
OpenDB

closeRS

'this is the connrection
OpenDB

'this is the error
rs.Open "SELECT Distinct [Inv #], [Cust #], [Date],[Due Date], sum[Amount] as InvBal FROM [data$] WHERE [data$].[Cust #]=" & "'" & cmbcustno.Text & "'", _
cnn, adOpenKeyset, adLockOptimistic


'dispose the connection
Set cnn = Nothing


If rs.RecordCount > 0 Then
Sheets("View").Visible = True
Sheets("View").Select
'Range("dataSet").Select
'Range(Selection, Selection.End(xlDown)).ClearContents

Range("A30").Select

'Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs

Else

'dispose the recordeset
Set rs = Nothing

Exit Sub
i got


End If


'dispose the recordeset
Set rs = Nothing



End Sub

 

[Colin Legg]

It should be sum([Amount]) rather than sum[Amount]. Additionally, when you use an aggregator function such as SUM in SQL you need to provide a GROUP BY clause. You also need to drop the DISTINCT keyword.

As I mentioned on your other thread (which seems to be very similar), you shouldn't use ADO to query data in open workbooks, including the workbook you're running the code in because you will get memory leaks.

Why don't you just use a pivot table to do this?

 

[Conrado Natac]

Hi Colin

Thanks for the tip. It did not work. What I want to do is to show the invoice balance using the "Apply to" field. Is is possible for you to look at this file and tell me the error. Basically, when added or sum the field "Apply to" and "Amount" will give the balance of an invoice.

I have been playing with this since yesterday.

'this gives the error
rs.Open "SELECT [Inv #], [Apply to], [Cust #], [Date],[Due Date], Sum([Amount]) as InvBal FROM [data$] WHERE [data$].[Type] = 'I' and" _
& " [data$].[Cust #]=" & "'" & cmbcustno.Text & "'" & "group by [Apply to]", _
cnn, adOpenKeyset, adLockOptimistic


Any help is greatly appreciated.

 

[NARAYANK991]

Hi Conrad ,

Try first with this simpler one :

strSQL = "SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] WHERE [data$].[Type] = 'I' and " & " [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"

Narayan

 

[Conrado Natac]

Thanks. This code works but the result is wrong. some of the invoice number on the "Apply to" is zero. for some reason, it did not add the negative amount per invoice or the payment.

I used the "Apply to" as common reference for an invoice. Thus, when you add any apply to using Amount some will have zero or fully paid.

Thanks a lot.

 

[Conrado Natac]

I got it now. I need to take out the where "I", because, it is only limiting it to payment.


strSQL = "SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] where [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"


Works now. yehey

Thanks a million.

 

[Conrado Natac]

I got it now. I need to take out the where "I", because, it is only limiting it to payment.


strSQL = "SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] where [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"


Works now. yehey

Thanks a million.

I need to take out the "I", so as not to limit it to invoice only. There type "P" as payment too.

Thanks

 

[Conrado Natac]

Hey Narayan,

I tried using a sub query but keeping getting an error. Can you please take a look at where the error is.

strSQL = "SELECT C1.[Cust Name], c1.[Date], C1.[Due Date] from [data$] as c1, [data$] where c1.[Cust #] in SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] where [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"


Any help is greatly appreciated. Thanks