Too few parameters

  • Thread starter Thread starter Tony C
  • Start date Start date
T

Tony C

Basil

What are you trying to do here? Reading this code, it
tells me either you are attempting to open a connection or
you are attempting to use VB to create/run a query? Which
is it?

Tony C.
-----Original Message-----
When I run this code in VBA it tells me I have too few
Click to expand...
parameters - but the parameters are declared within the
SQL.
Set rstRspnsCount = dbs.OpenRecordset("PARAMETERS
Click to expand...
[Forms]![frmAnalysis]![txtstdate] DateTime, [Forms]!
[frmAnalysis]![txtendate] DateTime; " _
& "SELECT Count(tblRespondents.RspnsID) AS
Click to expand...
CountOfRspnsID FROM tblRespondents " _
& "WHERE (((tblRespondents.RspnsDate) Between
Click to expand...
[Forms]![frmAnalysis]![txtstdate] And [Forms]!
[frmAnalysis]![txtendate]));")
 
I was running a query to determine what the record count of another query would be

I got it to work in the end by referencing the form outside of the SQL i.e

WHERE (((tblRespondents.RspnsDate) Between " & Format(txtstdate,"0") & " And .... and so on

The reason for formatting as "0" is because SQL from VBA doesn't follow regional dd/mm/yyy settings - If you live outside the US and are modifying queries through VBA you would really notice this

Basi

----- Tony C wrote: ----

Basi

What are you trying to do here? Reading this code, it
tells me either you are attempting to open a connection or
you are attempting to use VB to create/run a query? Which
is it

Tony C
-----Original Message----
When I run this code in VBA it tells me I have too few
Click to expand...
parameters - but the parameters are declared within the
SQL[Forms]![frmAnalysis]![txtstdate] DateTime, [Forms]
[frmAnalysis]![txtendate] DateTime; "
& "SELECT Count(tblRespondents.RspnsID) AS
Click to expand...
CountOfRspnsID FROM tblRespondents "
& "WHERE (((tblRespondents.RspnsDate) Between
Click to expand...
[Forms]![frmAnalysis]![txtstdate] And [Forms]
[frmAnalysis]![txtendate]));"
 
WHERE (((tblRespondents.RspnsDate) Between " & Format(txtstdate,"0") &
" And .... and so on.

The reason for formatting as "0" is because SQL from VBA doesn't
follow regional dd/mm/yyy settings - If you live outside the US and
are modifying queries through VBA you would really notice this.
Click to expand...

Nononononononono -- it's because of us other 94% of the world that don't
use USA dates that SQL ignores regional settings. If all the queries had to
be changed just because the swedish temp changed her control panel
settings, any kind of programming would be impossible! Therefore, there are
two safe formats:

ISO yyyy-mm-dd (note century digits, and use of hyphens), and

USA mm/dd/yyyy

any other formats _may_ be accepted as legal but _may_ _not_ be interpreted
correctly. And a silent error is probably worse than a debug message.

Forcing the serial date into a number is asking for troubles. Macintoshes
have a different base date from PCs, and some Excel-based applications will
have a different one too. Using a plain number will make it extremely
difficult to catch errors and trap some really nasty bugs. Do you want to
wait four years before sending any invoices out?

Please use a proper Jet-compatible date format: it'll save you handfuls of
hair in the medium term! And, as I said above, you don't have to worry
about USA and non-US users.

Hope that helps


Tim F
 
Thanks Tim, you've made this very clear to me (and probably others who might search this thread)

I'll use ISO dates in future (the logic for setting up US format like that baffles me!

Thanks again Tim

Basi

----- Tim Ferguson wrote: ----

WHERE (((tblRespondents.RspnsDate) Between " & Format(txtstdate,"0") &> " And .... and so on.
follow regional dd/mm/yyy settings - If you live outside the US an
are modifying queries through VBA you would really notice this.
Click to expand...

Nononononononono -- it's because of us other 94% of the world that don't
use USA dates that SQL ignores regional settings. If all the queries had to
be changed just because the swedish temp changed her control panel
settings, any kind of programming would be impossible! Therefore, there are
two safe formats

ISO yyyy-mm-dd (note century digits, and use of hyphens), an

USA mm/dd/yyyy

any other formats _may_ be accepted as legal but _may_ _not_ be interpreted
correctly. And a silent error is probably worse than a debug message.

Forcing the serial date into a number is asking for troubles. Macintoshes
have a different base date from PCs, and some Excel-based applications will
have a different one too. Using a plain number will make it extremely
difficult to catch errors and trap some really nasty bugs. Do you want to
wait four years before sending any invoices out

Please use a proper Jet-compatible date format: it'll save you handfuls of
hair in the medium term! And, as I said above, you don't have to worry
about USA and non-US users

Hope that help


Tim
 
Back
Top