VBA SQL SELECT SUM GROUP not working

[Kobus-Dippenaar]

Helllo, to all,
I have a select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. I have checked various helps, including VBA's help function, BUT the code is not working.

strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"

Your assistance is appreciated,

Blessings.

Kobus

 

[plog]

Why do you have that string in 3 parts? What are you accomplishing with the ampersands (&)?

The way to debug SQL is by putting your SQL in a query, running and debugging it there and then moving it back into your VBA. So what happens when you paste:

SELECT Account, Dept,Sum([Amount]) As [totamt] FROM TB201410 GROUP BY Account, Dept;

into a query?

 

[Kobus-Dippenaar]

Hello,
The [FONT=&quot]ampersands (&)? --- According to the VBA help it was set out as separate strings being concatenated. I just thought maybe the compiler doesn't like the syntax, once had something similar.

OK! I will setup a query, and then reference it in the sql string? Is that the idea?
Blessings,
[/FONT]

 

[smig]

Try to create in the query builder to make sure its working and copy the sql string.

 

[plog]

I will setup a query, and then reference it in the sql string?

That's not what I initially proposed but it will work. My suggestion was to debug your SQL in a query, then copy that SQL into your VBA.

 

[Kobus-Dippenaar]

Re: [Resolved] VBA SQL SELECT SUM GROUP not working

@Smig,
@plog,

@plog: I still need to do the SQL debug thing, not quite sure, but I I think to understand that I can use the SQL tab within the query module to get it to work?

In the mean time, herewith the code.
The sumOfAmount is not transferred and causes a null entry in the destination file, (Table).

Appreciate your guidance,

Blessings. Ooops the code....

'...................................Kobus New Routine to import TB
Dim db As DAO.Database
Dim rstTB As DAO.Recordset
Dim rstID As DAO.Recordset
Set rstID = Application.CurrentDb.OpenRecordset("00 Imported data", dbOpenDynaset)
Dim strSQL As String
' strSQL = "SELECT Account, Dept, Sum(Amount) AS [totx] FROM TB201410 GROUP BY Account, Dept"
strSQL = "SELECT TB201410.Account, TB201410.Dept, Sum(TB201410.Amount) AS SumOfAmount FROM TB201410 GROUP BY TB201410.Account, TB201410.Dept ORDER BY TB201410.Account, TB201410.Dept;"
Set db = CurrentDb
Set rstTB = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rstTB.EOF = False Then GoTo P05
MsgBox "No data in input file"
GoTo P9999
P05:
rstTB.MoveFirst
MsgBox ("first Record, [Account_Number]")
If rstTB!Account = "Account" Then GoTo P8000
P10:
If rstTB.EOF = True Then GoTo P9999
WSkey = rstTB!Account & " " & rstTB!Dept
Me.Text70 = WSkey
rstID.AddNew
rstID!Account = rstTB!Account
rstID!Dept = rstTB!Dept
rstID!Amount = SumOfAmount
rstID.Update
P8000:
rstTB.MoveNext
GoTo P10


P9999:
End Sub