Runtime error 3061 Too Few parameters, Expected 1 (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
Don't know which topic to put this under but since the report was the problem it is here. Originally written yesterday, July 31, which when reading my question makes more sense.

I have invoices I need to print each month, mostly to the same clients. While I want to print the before the first of the month, I want to show the Invoice Date as the first of the following month. I.E. If I printed them yesterday or today, I want them to print August 1, 2018. First I thought, let's just use this is the Invoice Date field:
Code:
  = Format(DateSerial(Year(Date()),1+Month(Date()),1),"mmmm d"", ""yyyy")
Works fine except that if I printed them tomorrow, it would be September 1, 2018.
So I used the following in the query to select a date based upon either an entered date on another form or based on the Date() of today.
Code:
  InvD: IIf(IsNull([Forms]![frmMainMenu]![InvoiceDate]),Format(DateSerial(Year(Date()),1+Month(Date()),1),"mmmm d"", ""yyyy"),Format([Forms]![frmMainMenu]![InvoiceDate],"mmmm d"", ""yyyy"))
The query works fine, that is, gives me the exact date I want. But when I try to put the field InvD on the report, I get an error message saying that:
Error Message "3061"
Too Few Parameters. Expected 1
My question is why. I have found that if I have all the fields for a report in my underlying query, I am less susceptible to errors showing up.
 

MarkK

bit cruncher
Local time
Today, 01:48
Joined
Mar 17, 2004
Messages
8,178
My guess is that the form reference fails. Is Forms!frmMainMenu open when you run the report?
Mark
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
Yes it is open.
 

MarkK

bit cruncher
Local time
Today, 01:48
Joined
Mar 17, 2004
Messages
8,178
...put the field InvD on the report...
How do you do this? And does this mean that if you don't put this field on the report, everything runs fine (except you don't have your date)?
Mark
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
Yes. If I use the code for InvD as = in an unbound text box it works fine. Bot if the InvD is in the query it doesn’t.
 

JHB

Have been here a while
Local time
Today, 09:48
Joined
Jun 17, 2012
Messages
7,732
Remove the double "" and I think you're missing the DateSerial function in the last format:
InvD: IIf(IsNull([Forms]![frmMainMenu]![InvoiceDate]),Format(DateSerial(Year(Date()),1+Month(Date()),1),"mmmm d"", ""yyyy"),Format([Forms]![frmMainMenu]![InvoiceDate],"mmmm d"", ""yyyy"))
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
I’ll try that tomorrow, but why would what I have work in the Query and not the report?
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
Finally got a chance to look at the suggestion from JHB in post #6, so far so good. Get the same result as I have before. If I open an invoice record, then open the report the field is populated properly from the query. But if I run the loop I get the error. The loop code (a test of the real code) is:


Code:
'declare a variable to hold an integer
Dim i As Long
'loop a certain number of times
Dim rpt As String
Dim cond As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

rpt = "rptInvoice3"

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryInvoicePrint")

With rs

For i = 1 To 2

cond = "[RecordID] = " & rs!RecordID
'Cannot open in acViewNormal as page length is too long.
DoCmd.OpenReport rpt, acViewPreview, , cond, , "A"
PrintNew
DoCmd.Close acReport, rpt
rs.MoveNext
Next i
End With

rs.Close
The code stops at:
Code:
Set rs = db.OpenRecordset("qryInvoicePrint")
 

lpapad

Registered User.
Local time
Today, 10:48
Joined
Jul 7, 2018
Messages
47
Just a simple advice, try first simple arguments, then replace them gradually by complex expressions, so that error can be spotted.
 

lpapad

Registered User.
Local time
Today, 10:48
Joined
Jul 7, 2018
Messages
47
It seems SQL is not properly defined. Try to debug runtime SQL query expression.
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
I have been trying simple versions of the query. The query works. it just doesn't work when it has to be the underlying data for the Report.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:48
Joined
Aug 30, 2003
Messages
36,118
The recordset can't resolve form references. Simplest solution is probably to wrap each in the Eval() function.
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
I tired your suggestion:


Code:
InvD1: Eval(IIf(IsNull([Forms]![frmMainMenu]![InvoiceDate]),Format(DateSerial(Year(Date()),1+Month(Date()),1),"mmmm d"", ""yyyy"),Format([Forms]![frmMainMenu]![InvoiceDate],"mmmm d"", ""yyyy")))
And still get "The expression is typed incorrectly or is too complex to be evaluated.


Yet:
Code:
InvD: IIf(IsNull([Forms]![frmMainMenu]![InvoiceDate]),Format(DateSerial(Year(Date()),1+Month(Date()),1),"mmmm d"", ""yyyy"),Format([Forms]![frmMainMenu]![InvoiceDate],"mmmm d"", ""yyyy"))
Results in the correct return in a query. But not when it is in the query that is the underlying data for the report.


Is this some sort of 'bug" in Access. That it cannot handle this or am I doing something wrong?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:48
Joined
Aug 30, 2003
Messages
36,118
Around each I said:

Eval("Forms!...")
 

isladogs

MVP / VIP
Local time
Today, 08:48
Joined
Jan 14, 2017
Messages
18,186
Earlier in the thread you were asked if your form was open when you run your report. Does it remain open? If not, Access may not be retaining the form values.

Hopefully the Eval function will solve this.

If not, then suggest you set variables or tempvars to represent the true and false part of your IIF statement and work with those in your report. If using variables, you will need to assign thee to functions for your report.
 
Last edited:

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
pbaldy: Eval tests fine. Returns exactly what I wanted.

ridders: The record form is open only for a test. That is, when the report is opened based on the data for only one record, everything goes as planned. It is just when the loop code comes into play that the error occurs.

Later on today I will try a few other things to see what is going on. I am going to set apart each of the conditions of the IIF statement and see if I can break it down where the error may be occurring. Just frustrating to know that in a query the results are OK, but not when it is used to support a report.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:48
Joined
Aug 30, 2003
Messages
36,118
What exactly do you mean by "support a report"? In the code you posted earlier, you were opening a recordset on the query. OpenRecordset can't handle form references in the query, thus your error (and the Eval() function can get around it). You should be able to base a report on the query without any modification.
 

Eljefegeneo

Still trying to learn
Local time
Today, 01:48
Joined
Jan 10, 2011
Messages
904
The report I open with the loop is called rptInvoice3. When I want to test it, I merely open a form based on tblInvoices and then click on the report to see it. Thus the record on the form is the data that populates the single Invoice report.


This works fine. But if I try to open the same report using the loop code, I get the error message.



Using query as a record set, if I don't have the Invoice Date in the query all is OK. However, if it is in the query, regardless if I have it on the report as a control, I get the error message that it is too complex. There are no form references in the underlying query to the report.



Haven't had a chance to est using eval() yet. Will do tomorrow. But, is my IFF statement really that complex?
 

Users who are viewing this thread

Top Bottom