Too few parameters error

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

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

--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

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

"Duane Hookom" wrote:

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

--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

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
--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

"Duane Hookom" wrote:

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

--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

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


"Duane Hookom" wrote:

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
--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

"Duane Hookom" wrote:

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

--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

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

Happy debugging...
--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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


"Duane Hookom" wrote:

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
--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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

"Duane Hookom" wrote:

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

--
Duane Hookom
Microsoft Access MVP


"Lesli" wrote:

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