Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

too few parameters: expected 2

Posted on 2011-03-17
21
Medium Priority
?
468 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 2000 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 58
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 58
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 58
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 58
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 58
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

877 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