• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

too few parameters: expected 2

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
audreymjhha
Asked:
audreymjhha
  • 6
  • 5
  • 5
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
audreymjhhaAuthor Commented:
Thank you both but I received syntax errors on both lines.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
audreymjhhaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
this

Between ## And ##)

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

0
 
Rey Obrero (Capricorn1)Commented:
where are you calling the codes?

is the form [frmExport] open?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

"No need for Eval() here."

audreymjhha:  Try the Eval() ...

:-)
0
 
audreymjhhaAuthor Commented:
I'm sorry - I didnt test the form correctly. Your code worked. Thank you
0
 
Rey Obrero (Capricorn1)Commented:


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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Certainly no need for Format$

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@MX,

<<Certainly no need for Format$>>

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

JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Format$ is additional overhead.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I would disagree; "
Does the Accepted Solution have Format$ ?

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@Mx,

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

  You must have missed my comment after that.

JimD.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now