Too few parameters error

[Guest]

Let me start off by saying that I'm working with someone else's code that I
only marginally understand. That said, here's my problem.

I have a summary report that is pulling counts of different types of things
from a query. My users have a form that they enter two controls
(txtStartDate and txtEndDate) where the enter the date range for the report
and then press a Preview Report button with the following OnClick event:
DoCmd.OpenReport "rptASPAPSummary", acPreview, , , ,
Me.txtStartDate & "%" & Me.txtEndDate.

The coding for the report is:

Private Sub Report_Open(Cancel As Integer)

Dim I As Integer
Dim myStr() As String
Dim myStartDate As String
Dim myEndDate As String

If Len(Me.OpenArgs) > 0 Then
myStr = Split(Me.OpenArgs, "%")
myStartDate = myStr(0)
myEndDate = myStr(1)
getSQL (myStartDate), (myEndDate)
End If

End Sub

Private Function getNumber(mySQL As String) As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Debug.Print mySQL
Set db = CurrentDb
Set rs = db.OpenRecordset(mySQL)
If rs!TotalofaintRecID > 0 Then
getNumber = rs!TotalofaintRecID
Else
getNumber = 0
End If
rs.Close
End Function

Private Sub getSQL(myStartDate As Date, myEndDate As Date)
Dim sSQL As String

Me.Text0.Caption = "Reporting Period: " & myStartDate & " to " & myEndDate

sSQL = _
"SELECT Count(aintRecID) AS TotalofaintRecID FROM
rptqselASPAPSummary WHERE adtmBHCSrcpt>=#" _
& myStartDate & "# AND adtmBHCSrcpt<=#" & myEndDate & "#"
Me.Text2.Caption = getNumber(sSQL) 'Count of requests

sSQL = _
"SELECT Count(aintRecID) AS TotalOfaintRecID FROM
rptqselASPAPSummary WHERE adtmBHCSrcpt>=#" _
& myStartDate & "# AND adtmBHCSrcpt<=#" & myEndDate & "# AND
astrDisposition='Approved'"
'Me.Text2.Caption = getNumber(sSQL) 'Count of approved requests

End Sub

When I try to open the report (after filling in the dates on the form and
clicking the Preview Report button) I get the error message: "Runtime error
"3061." To few parameters. Expected 4". When I click the debug button it
highlights the Set rs = db.OpenRecordset(mySQL) line in the getNumber
function.

I have no idea how to fix this. There are 22 sSQL statements in total, but
I've only listed the first two. I'm not going to worry about the rest until I
can get at least one working correctly.

Can someone help please?

Thanks,
Lesli

 

[Guest]

Are you entering dates into the controls? Did you change any queries or field
names?

 

[Guest]

Yes, I'm entering dates in the controls on the form. the txtStartDate and
txtEndDate on the form are unbound (don't know if that matters). But I don't
know how to check if the dates are actually getting passed to the report when
it opens.

Lesli

 

[Guest]

You should learn how to debug code. You can set break points or use code like:
Private Function getNumber(mySQL As String) As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Msgbox mySQL 'should display the sql
Debug.Print mySQL 'puts the sql string into the immediate/debug window
' you can then press Ctrl+G and copy the SQL into a new blank query
sql view

 

[Guest]

Holy cow, I wish I'd learned to do this a long time ago. I was able to find
the problem (and a couple others) and fix them in very little time!

Thank you for your help. I learn something new everytime I come out here.

Lesli

 

[Guest]

It's good to hear that I taught you how to fish rather than handing you a
fish...

Happy debugging...