Solved

too few parameters: expected 2

Posted on 2011-03-17
21
437 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 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