select query

[Gr3g0ry]

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]

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

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]

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]

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]

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]

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]

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]

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

 

[arnelgp]

check DrawAmount field if this id correct

 

[MarkK]

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]

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]

Yes check for EOF in the recordset

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

 

[arnelgp]

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

 

[MarkK]

Another code idea...

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