asked on

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

Rey Obrero (Capricorn1)

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] & "#));"

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

audreymjhha

ASKER

Thank you both but I received syntax errors on both lines.

audreymjhha

ASKER

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.

Rey Obrero (Capricorn1)

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] & "#"

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

where are you calling the codes?

is the form [frmExport] open?

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

Jim Dettman (EE MVE)

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.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"No need for Eval() here."

audreymjhha: Try the Eval() ...

:-)

audreymjhha

ASKER

I'm sorry - I didnt test the form correctly. Your code worked. Thank you

Rey Obrero (Capricorn1)

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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Certainly no need for Format$

mx

Jim Dettman (EE MVE)

<<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.

Jim Dettman (EE MVE)

@MX,

<<Certainly no need for Format$>>

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

JimD.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Format$ is additional overhead.

mx

Jim Dettman (EE MVE)

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.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

mx

Jim Dettman (EE MVE)

@Mx,

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

You must have missed my comment after that.

JimD.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

@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.