If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Too few parameters error
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 |
#3
|
|||
|
|||
Too few parameters error
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 |
#4
|
|||
|
|||
Too few parameters error
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 |
#5
|
|||
|
|||
Too few parameters error
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 |
#6
|
|||
|
|||
Too few parameters error
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 |
Thread Tools | |
Display Modes | |
|
|