Please Help, RunTime 3061 Too few Parameters

[Jackson via AccessMonster.com]

Hi,

I've struggled with this a few times to understand what the problem is and
all posts I've seen never seemed to have a concrete answer, this seems to be
some voodoo area of access. I'm simply trying to open a recordset which is a
query (no criteria on it) so I can process records and create a file, start
of code for the Public Sub is:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim dteRecDate As Date

dteRecDate = Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=dteRecDate")

The last line is where it fails, I get 'Run-time error '3061' Too few
parameters. Expected 1.'

I've declared the date and written the criteria in VBA so I can't work out
why access doesn't understand my criteria? If I run it as:
Set rs = db.OpenRecordset("qryLIVEFXTradesTDP")
It will open the recordset fine, obviously with more data than I'm after...
please help if you know what I'm doing wrong or have any ideas?

 

[fredg]

Hi,

I've struggled with this a few times to understand what the problem is and
all posts I've seen never seemed to have a concrete answer, this seems to be
some voodoo area of access. I'm simply trying to open a recordset which is a
query (no criteria on it) so I can process records and create a file, start
of code for the Public Sub is:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim dteRecDate As Date

dteRecDate = Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=dteRecDate")

The last line is where it fails, I get 'Run-time error '3061' Too few
parameters. Expected 1.'

I've declared the date and written the criteria in VBA so I can't work out
why access doesn't understand my criteria? If I run it as:
Set rs = db.OpenRecordset("qryLIVEFXTradesTDP")
It will open the recordset fine, obviously with more data than I'm after...
please help if you know what I'm doing wrong or have any ideas?

Your code, if you could display a MsgBox after stepping through the
code, would return a message like:
SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt] =dteRecDate

Note there is no space between the = and the word dteRecDate, and it
shows the variable name dteRecDate, not the variable value.

How about enclosing the date variable within the date delimiter
symbols (#), placing a space after the = sign, and concatenating the
variable value into the statement.

Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE
[trade_ldt] = #" & dteRecDate & "#")


The above change, if displayed in a MsgBox after stepping through the
code, should return a message like:

SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt] = #4/7/2009#

 

[mcescher]

Hi,

I've struggled with this a few times to understand what the problem is and
all posts I've seen never seemed to have a concrete answer, this seems tobe
some voodoo area of access. I'm simply trying to open a recordset which is a
query (no criteria on it) so I can process records and create a file, start
of code for the Public Sub is:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim dteRecDate As Date

dteRecDate = Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=dteRecDate")

The last line is where it fails, I get 'Run-time error '3061' Too few
parameters. Expected 1.'

I've declared the date and written the criteria in VBA so I can't work out
why access doesn't understand my criteria? If I run it as:
Set rs = db.OpenRecordset("qryLIVEFXTradesTDP")
It will open the recordset fine, obviously with more data than I'm after....
please help if you know what I'm doing wrong or have any ideas?

Try something like this:
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE
[trade_ldt]=" & dteRecDate)

But since it's a date, you may need to enclose it with pound signs
like this:
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE
[trade_ldt]= #" & dteRecDate & "#")

Hope this helps,
Chris M.

 

[dymondjack]

I've struggled with this a few times to understand what the problem is and

all posts I've seen never seemed to have a concrete answer, this seems to be
some voodoo area of access.

For the record... the answer to this error is almost ALWAYS an error in the
syntax of the SQL string, most commonly a space that needs to be there and
got missed, or a variable not being entered correctly. Its the first two
things to check as soon as you see this error.

It seems as though fredg has given some suggestions on what you need to do
to get syntax where it should be.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

Hi,

I've struggled with this a few times to understand what the problem is and
all posts I've seen never seemed to have a concrete answer, this seems to be
some voodoo area of access. I'm simply trying to open a recordset which is a
query (no criteria on it) so I can process records and create a file, start
of code for the Public Sub is:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim dteRecDate As Date

dteRecDate = Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=dteRecDate")

The last line is where it fails, I get 'Run-time error '3061' Too few
parameters. Expected 1.'

I've declared the date and written the criteria in VBA so I can't work out
why access doesn't understand my criteria? If I run it as:
Set rs = db.OpenRecordset("qryLIVEFXTradesTDP")
It will open the recordset fine, obviously with more data than I'm after...
please help if you know what I'm doing wrong or have any ideas?

 

[Jackson via AccessMonster.com]

Ok, have got somewhat somewhere, realised the date in the query was actually
a string so changed the code a bit, see below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim strRecDate As String

strRecDate = CStr(Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy"))

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=" & "'strRecDate'")
*added rs.MoveFirst as it appeared no records are being found, getting RT
3021, No current Record. Thus something's not working correctly on the
criteria, though it's not giving parameter errors anymore. If I set it to <
rather than =, it will return all records, including the criteria that it
should be excluding. Bizarre, any further ideas?

 

[Jackson via AccessMonster.com]

Ok, have got somewhat somewhere, realised the date in the query was actually
a string so changed the code a bit, see below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim strRecDate As String

strRecDate = CStr(Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy"))

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=" & "'strRecDate'")
*added rs.MoveFirst as it appeared no records are being found, getting RT
3021, No current Record. Thus something's not working correctly on the
criteria, though it's not giving parameter errors anymore. If I set it to <
rather than =, it will return all records, including the criteria that it
should be excluding. Bizarre, any further ideas?

 

[John W. Vinson]

Ok, have got somewhat somewhere, realised the date in the query was actually
a string so changed the code a bit, see below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intFileNum As Integer
Dim strExportFile As String
Dim varHeader As Variant
Dim strDelimiter As String
Dim varTrades As Variant
Dim strRecDate As String

strRecDate = CStr(Format([Forms]![frmHome]![dteRecDate], "dd-mmm-yyyy"))

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
=" & "'strRecDate'")
*added rs.MoveFirst as it appeared no records are being found, getting RT
3021, No current Record. Thus something's not working correctly on the
criteria, though it's not giving parameter errors anymore. If I set it to <
rather than =, it will return all records, including the criteria that it
should be excluding. Bizarre, any further ideas?

If the table field containing date data is a Text field, then Access won't be
able to apply any of its sophisticated date interpretation: the string stored
in the table and the string passed as a parameter must match EXACTLY,
character for character. "3-Apr-2009" and "03-Apr-2009" are two completely
different strings, as strings.

What are some representative values of [trade_ldt]? Exactly how are they
formatted and stored? Do they EXACTLY match the dd-mmm-yyyy format?

Note that the Cstr() in your expression is unneeded: the Format() function
already returns a String value.

The root of the problem appears to be that you're using the wrong datatype in
your table: if you store date/time data in a date/time field it's a lot
simpler.

 

[Jackson via AccessMonster.com]

Ok, I've sorted out a fix for this. I changed the date field in the query to
a date format using CDate().
Then, the real nuance for getting this to work was that VBA required the
criteria date format to be set as:

dteRecDate = Format([Forms]![frmHome]![dteRecDate], "m/d/yyyy")
Set rs = db.OpenRecordset("SELECT * FROM qryLIVEFXTradesTDP WHERE [trade_ldt]
= #" & dteRecDate & "#")

The date had to be set explicitly in that American format for some bizarre
reason.

This makes me think I could set the dteRecDate from the form as the query
criteria by declaring the Form field in the format VBA recognises.

Thanks for help, wouldn't have got it without some basic
conselling/formatting advice from you guys.