Solved

too few parameters: expected 2

Posted on 2011-03-17
21
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

628 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