Report VBA Question 1

[Ray1127]

I have a query that returns a single value. It runs in Query Analyzer in Access 2000 perfectly. However I need to run this query to populate a text box in a Report. I created a function that returns the value the query returns. Here's the function

Public Function get_Issues(ByRef varProvGrp As String, varProvsubgrp As String) As Long
Dim strsql As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

strsql = "SELECT Sum([CT]) AS SumOfCT " & _
"FROM [0008_PCP Transfers by Group - with MM] " & _
"Where ProvGroupName = """ & varProvGrp & _
""" and PROV_SUB_GRP_NAME = """ & varProvsubgrp & """"
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
get_Issues = rs!SumOfCT
Else
get_Issues = 0
End If

rs.Close
Set rs = Nothing

End Function

If I run the report I get an error in the rs.open statment above stating no value given for one or more required parameters. The query is not that complicated and both parameters are provided what's missing?

 

[SkipVought]

Use the APOSTROPHY as your string delimiter...

PROV_SUB_GRP_NAME = "'" & varProvsubgrp & "'"

so that the result in the string, if varProvsubgrp contained SkipVought,...
[tt]
PROV_SUB_GRP_NAME = 'SkipVought'

[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWise]

Skip:
should it not be

PROV_SUB_GRP_NAME = '" & varProvsubgrp & "'"

 

[PWise]

Sorry

" PROV_SUB_GRP_NAME = '" & varProvsubgrp & "'"

 

[Ray1127]

Yep that was it. Thanks

 

[SkipVought]

Yes!

and what is should have posted, as I often suggest...

    strsql = "SELECT Sum([CT]) AS SumOfCT "
    strsql = strsql & "FROM [0008_PCP Transfers by Group - with MM] "
    strsql = strsql & "Where ProvGroupName = '" & varProvGrp & "'"
    strsql = strsql & "  and PROV_SUB_GRP_NAME = '" & varProvsubgrp & "'"

as I always code my SQL string.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]