Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-01-2018, 03:48 PM   #1
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Runtime error 3061 Too Few parameters, Expected 1

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.

__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-01-2018, 05:18 PM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,285 Times in 1,224 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
My guess is that the form reference fails. Is Forms!frmMainMenu open when you run the report?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-01-2018, 06:47 PM   #3
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

Yes it is open.

__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-01-2018, 07:22 PM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,285 Times in 1,224 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by Eljefegeneo View Post
...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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-01-2018, 08:45 PM   #5
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

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.
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-01-2018, 09:06 PM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,321
Thanks: 2
Thanked 1,959 Times in 1,915 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Runtime error 3061 Too Few parameters, Expected 1

Remove the double "" and I think you're missing the DateSerial function in the last format:
Quote:
InvD: IIf(IsNull([Forms]![frmMainMenu]![InvoiceDate]),Format(DateSerial(Year(Date()),1+Month(Date()),1 ),"mmmm d"", ""yyyy"),Format([Forms]![frmMainMenu]![InvoiceDate],"mmmm d"", ""yyyy"))
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 08-01-2018, 09:48 PM   #7
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

I’ll try that tomorrow, but why would what I have work in the Query and not the report?

__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-05-2018, 07:06 AM   #8
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

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")
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-05-2018, 01:13 PM   #9
lpapad
Newly Registered User
 
Join Date: Jul 2018
Location: Greece
Posts: 47
Thanks: 12
Thanked 4 Times in 4 Posts
lpapad is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

Just a simple advice, try first simple arguments, then replace them gradually by complex expressions, so that error can be spotted.
lpapad is offline   Reply With Quote
Old 08-05-2018, 01:15 PM   #10
lpapad
Newly Registered User
 
Join Date: Jul 2018
Location: Greece
Posts: 47
Thanks: 12
Thanked 4 Times in 4 Posts
lpapad is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

It seems SQL is not properly defined. Try to debug runtime SQL query expression.
lpapad is offline   Reply With Quote
Old 08-05-2018, 09:16 PM   #11
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

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.
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-06-2018, 05:54 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,850
Thanks: 9
Thanked 3,827 Times in 3,770 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Runtime error 3061 Too Few parameters, Expected 1

The recordset can't resolve form references. Simplest solution is probably to wrap each in the Eval() function.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Eljefegeneo (08-07-2018)
Old 08-07-2018, 08:16 PM   #13
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 681
Thanks: 247
Thanked 28 Times in 28 Posts
Eljefegeneo is on a distinguished road
Re: Runtime error 3061 Too Few parameters, Expected 1

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?
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 08-07-2018, 09:29 PM   #14
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,850
Thanks: 9
Thanked 3,827 Times in 3,770 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Runtime error 3061 Too Few parameters, Expected 1

Around each I said:

Eval("Forms!...")
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Eljefegeneo (08-08-2018)
Old 08-07-2018, 11:25 PM   #15
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,182
Thanks: 83
Thanked 1,524 Times in 1,421 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Runtime error 3061 Too Few parameters, Expected 1

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est

Last edited by ridders; 08-08-2018 at 12:04 AM.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Eljefegeneo (08-08-2018)
Reply

Tags
runtime error 3061

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] [Help]Error: 3061 = Too few Parameters. Expected 1 Pipo Modules & VBA 12 04-14-2016 08:58 PM
Error 3061 Too few parameters expected 2. SJB67 Modules & VBA 5 10-07-2015 12:02 AM
Error 3061: Too Few Parameters. Expected 2 Ally Modules & VBA 27 02-05-2015 02:50 PM
Runtime Error 3061 Too Few Parameters Expected 3 patelr26 Modules & VBA 5 07-15-2008 04:23 AM
Help! - Runtime Error 3061:Too Few parameters Expected CBO Modules & VBA 2 08-08-2005 10:42 AM




All times are GMT -8. The time now is 03:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World