select query (1 Viewer)

Gr3g0ry · Dec 13, 2017

hi. this query is giving me issues. i created it in Access query design n then copied it over to vba. does not work n i cant seem to figure out why. plz help

strSql = "SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS GROUP BY DRAWS.PartnerId HAVING (((DRAWS.PartnerId)= " & cust & "));"

Set rst = CurrentDb.OpenRecordset(strSql)

total = rst!SumOfDrawAmount
MsgBox "The valu of loans is " + total

the rrror is here.
Set rst = CurrentDb.OpenRecordset(strSql)

to few parameters it says

Minty · Dec 13, 2017

I would change that around to a Where - using "Having" unnecessarily is a slow way to write that query.

Your problem is caused by it not knowing what Cust is. In the original query I guess it asked you for it, and I assume it's a number?

1st - Put Option Explicit at the top of all your code modules.
2nd Try this

Code:

Dim Cust as Long

Cust = 1234

strSql = "SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS WHERE DRAWS.PartnerId = " & cust & " GROUP BY DRAWS.PartnerId ;"

Debug.Print strSql

Set rst = CurrentDb.OpenRecordset(strSql)

total = rst!SumOfDrawAmount
MsgBox "The value of loans is " + total

arnelgp · Dec 13, 2017

put it in msgbox and see what is the display:

strSql = "SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS GROUP BY DRAWS.PartnerId HAVING (((DRAWS.PartnerId)= " & cust & "));"

msgbox strSQL
exit sub

Gr3g0ry · Dec 13, 2017

Dim strSql As String
Dim total As Double
Dim cust As Long

cust = Me.cboPartnerId.Value
MsgBox cust

'strSql = "SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS GROUP BY DRAWS.PartnerId HAVING (((DRAWS.PartnerId)= " & cust & "));"

strSql = "SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS WHERE DRAWS.PartnerId = " & cust & " GROUP BY DRAWS.PartnerId ;"

Debug.Print strSql

Set rst = CurrentDb.OpenRecordset(strSql)
' new code:
'total = rst!SumOfDrawAmount
'MsgBox total

once i include Set rst = CurrentDb.OpenRecordset(strSql) i get the following error:

Run-time error '3061'
Too few parameters. Expected 1.

im not sure what else to do

Minty · Dec 13, 2017

If you open the immediate window (Press ctrl G in the debug vba screen) you should see the SQL String.

Paste that here and/or into the query editor to see what happens.

arnelgp · Dec 13, 2017

what is cust? is it in the form. if it is
use Me![cust].
is it numeric?
no need to group just sum it.

strSql = "SELECT Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS WHERE DRAWS.PartnerId = " & [cust] & ";"

Gr3g0ry · Dec 13, 2017

Minty said:
If you open the immediate window (Press ctrl G in the debug vba screen) you should see the SQL String.

Paste that here and/or into the query editor to see what happens.

SELECT DRAWS.PartnerId, Sum(DRAWS.DrawAmount) AS SumOfDrawAmount FROM DRAWS WHERE DRAWS.PartnerId = 23 GROUP BY DRAWS.PartnerId ;

this is what i get.

arnelgp

ive done all uve suggested. all works. cust is an integer value.

the resultset is what is giving an issue:
Set rst = CurrentDb.OpenRecordset(strSql)

Run-time error '3061'
Too few parameters. Expected 1.

Minty · Dec 13, 2017

And if you paste that into the query editor under SQL view does it run ?

arnelgp · Dec 13, 2017

check DrawAmount field if this id correct

MarkK · Dec 13, 2017

Gr3g0ry said:
Run-time error '3061'
Too few parameters. Expected 1.

This error occurs if there is an identifier that the SQL parser cannot identify. Any unknown identifier is assumed to be a parameter, and if you don't supply a value for this parameter at query execution time, this error occurs.

The most common cause is that you are using a field name in the SQL that does not exist in the table. Check all your spelling. Check the table DRAWS for the fields PartnerID and DrawAmount

hth
Mark

Gr3g0ry · Dec 13, 2017

yeah ... thanks. fixed.

so here is another issue. sometimes the sum will return nothing due to the fact that there may not yet be entires in the DRAWS table.

if there is no summations then, totaldraws = rst!SumOfDrawAmt throws an

error Run-time error '3012':
No current record.

is there a way that i can say

if (totaldraws = rst!SumOfDrawAmt) gives error then
totaldraws = 0;

else totaldraws = rst!SumOfDrawAmt

in vba ?

Minty · Dec 13, 2017

Yes check for EOF in the recordset

Code:

If rst.EOF then
    total = 0
Else
    total = rst!SumOfDrawAmount
End If 
MsgBox "The value of loans is " + total

arnelgp · Dec 13, 2017

if rst. bof and rst. eof then
totaldraws=0
else
rst. movefirst
totaldraws=rst("SumOfDrawAmount")
end if

MarkK · Dec 13, 2017

Another code idea...

Code:

Function GetLoansByCustomer(CustomerID As Long) As Currency
    Const SQL_SELECT As String = _
        "SELECT Sum(DrawAmount) " & _
        "FROM DRAWS " & _
        "WHERE PartnerId = "
    
    With CurrentDb.OpenRecordset(SQL_SELECT & CustomerID)
        If Not .EOF Then GetLoansByCustomer = .Fields(0)
        .Close
    End With
End Function

hth
Mark

select query (1 Viewer)

Gr3g0ry

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Gr3g0ry

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Gr3g0ry

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

MarkK

bit cruncher

Gr3g0ry

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

MarkK

bit cruncher

Similar threads

Users who are viewing this thread