Solved

too few parameters: expected 2

Posted on 2011-03-17
21
428 Views
Last Modified: 2013-11-27
I am trying to integrate date parameters from form into sql statement so I only pull records based on the user defined date range. I receive the too few parameters error. If I substitute #03/01/2011#, the program executes.
------------------------------------------------------------------------------------------------------------
Set appExcel = New Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set wks = appExcel.Worksheets(cTabTwo)  
   sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE (((tblmain.[Refer date]) Between [Forms]![frmExport].[txtFromReferDate] And [Forms]![frmExport].[txtThruReferDate]));"
 
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) ****I error out on this line of code***

Both the table and the form text boxes are defined as short dates. Does anyone have any suggestions?
Thank you,
Audrey
 

0
Comment
Question by:audreymjhha
  • 6
  • 5
  • 5
  • +2
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35157864
change this

   sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE (((tblmain.[Refer date]) Between [Forms]![frmExport].[txtFromReferDate] And [Forms]![frmExport].[txtThruReferDate]));"
 

with


   sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE (((tblmain.[Refer date]) Between #" & [Forms]![frmExport].[txtFromReferDate] &"# And #" & [Forms]![frmExport].[txtThruReferDate] & "#));"
 
0
 
LVL 75
ID: 35157876
Try making this change:


   sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE (((tblmain.[Refer date]) Between EVAL("[Forms]![frmExport].[txtFromReferDate]") And EVAL("[Forms]![frmExport].[txtThruReferDate]") ));"

mx
0
 

Author Comment

by:audreymjhha
ID: 35158099
Thank you both but I received syntax errors on both lines.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:audreymjhha
ID: 35158131
Capricorn1,
   The error message I received was the following
Watch :   : ExportRequest : "Syntax error in date in query expression '(((tblmain.[Refer date]) Between ## And ##)'." : String : Form_frmExport.ExportRequest.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35158153
audreymjhha,

post what you have that is causing syntax error.

or copy and paste this


  sSQL = "SELECT tblmain.* FROM tblmain WHERE tblmain.[Refer date] Between #" & [Forms]![frmExport].[txtFromReferDate] & "# And #" & [Forms]![frmExport].[txtThruReferDate] & "#"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35158165
this

Between ## And ##)

means that there are no values in [txtFromReferDate]  and [txtThruReferDate]..

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35158185
where are you calling the codes?

is the form [frmExport] open?
0
 
LVL 75
ID: 35158222
Try this:

   sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE (((tblmain.[Refer date]) Between " & Eval("[Forms]![frmExport].[txtFromReferDate]") & " And " & Eval("[Forms]![frmExport].[txtThruReferDate]")


mx
0
 
LVL 57
ID: 35158277
No need for Eval() here.  SQL should be:

  sSQL = "SELECT tblmain.*, tblmain.[Refer date] FROM tblmain WHERE ((tblmain.[Refer date] Between #" & Format$([Forms]![frmExport].[txtFromReferDate],"mm/dd/yy") & "# And #" & Format$([Forms]![frmExport].[txtThruReferDate],"mm/dd/yy") & "#));"

JimD.
0
 
LVL 75
ID: 35158291

"No need for Eval() here."

audreymjhha:  Try the Eval() ...

:-)
0
 

Author Closing Comment

by:audreymjhha
ID: 35158318
I'm sorry - I didnt test the form correctly. Your code worked. Thank you
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35158326


whatever you try, if there are  no values on
[txtFromReferDate]  and [txtThruReferDate]..

the sql statement will still fail, unless we use an IIF substituting values if
[txtFromReferDate]  and [txtThruReferDate]

are empty
0
 
LVL 21
ID: 35158344
FYI:
The cause of this error is that you can't include form references embedded  in an  SQL statement that is used to open a recordset. You must substitute the parameter values into the SQL statement.


0
 
LVL 75
ID: 35158405
Certainly no need for Format$

mx
0
 
LVL 57
ID: 35158409
<<The cause of this error is that you can't include form references embedded  in an  SQL statement that is used to open a recordset. You must substitute the parameter values into the SQL statement.>>

  Well that's not entirely true.  You could have plunked the SQL as it stood into a query def, then looped through the parameters collection and used Eval() to resolve the form references.

  In affect your doing the same thing as placing the values in there yourself, but your not doing it directly.  In this case, that would just be extra work because the SQL string could be built on the fly.  Which is also why Eval was not required.  Eval would have just been additional overhead as you already can get the value of the controls without it.

JimD.
0
 
LVL 57
ID: 35158433
@MX,

<<Certainly no need for Format$>>

  I would disagree; JET SQL expects date expressions to be in USA format

JimD.
0
 
LVL 75
ID: 35158436
Format$ is additional overhead.

mx
0
 
LVL 57
ID: 35158439

  I should add that it does work the other way for the most part, but you can get caught if someone changes their regional settings.

  Because of that, I always format my date expressions in SQL.

JimD.
0
 
LVL 75
ID: 35158451
"I would disagree; "
Does the Accepted Solution have Format$ ?

mx
0
 
LVL 57
ID: 35158488
@Mx,

<<"I would disagree; "
Does the Accepted Solution have Format$ ?>>

  You must have missed my comment after that.

JimD.
0
 
LVL 21
ID: 35158547
@JDettman

<<You could have plunked the SQL as it stood into a query def, then looped through the parameters collection>>
Exactly. It is still a fact that You must substitute the parameter values into the SQL statement. It was not specifying any method. There are multiple ways to do this. You just pointed out a different method than what has been previously suggested. I prefer to use the QueryDef() method in some situations.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question