Recordset based on a SQL string gives 'Too few parameters' error -- Access97

[Leonard Priestley]

I am trying to restrict a recordset to those records which concern a
particular person, and which lie between two dates. The start and finish
dates are specified in two textboxes - txtStartDate and txtEndDate - and the
PersonID is taken from a textbox, txtPersonID, which is bound to the field
[PersonID] in a table.

So far I have this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [PersonID], [DateOfInterest] FROM tblShiftTime " & _
"WHERE [PersonID] = Me.txtPersonID AND [DateOfInterest] >= Me.txtStartDate
" & _
"AND [DateOfInterest] <= Me.txtEndDate"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveFirst

When I try to run this, I get the error message "Too few parameters:
Expected 3"
This seems to be referring to the three fields specified in the WHERE
statement. If I comment the
WHERE statement out, I do not get an error message, and if I reduce the
number of fields referred to,
the error message changes the number of expected parameters accordingly.
The problem arises at the
line: Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
At the time I run the code, all three textboxes referred to have sensible
data in them.

Any ideas anyone?

Leonard Priestley

 

[Nikos Yannacopoulos]

Leonard,

Your references to the controls on the form must be left out of the quotes,
otherwise VBA doesn't treat them as such. Try changing your strSQL to:

strSQL = "SELECT [PersonID], [DateOfInterest] FROM tblShiftTime"
strSQL = strSQL & " WHERE [PersonID] = '" & Me.txtPersonID & "'"
strSQL = strSQL & " AND [DateOfInterest] >=" & Me.txtStartDate
strSQL = strSQL & " AND [DateOfInterest] <=" & Me.txtEndDate

where I have assumed that PersonID is text. In case it is numeric, change
the second line to:
strSQL = strSQL & " WHERE [PersonID] = " & Me.txtPersonID

Also, keep in mind that in VBA all dates are US format (month/day/year), so
if your date fields are formatted differently you may need to make some
changes there to get it to filter correctly.

HTH,
Nikos

 

[Leonard Priestley]

Nikos,

Thank you for your help. The syntax you suggested was just what was needed.
You were also correct in your comment about the US date format (I live in
New Zealand). In case you're interested, I used the following date format:

" AND [DateOfInterest] >= # " & Format(Me.txtStartDate,
"YYYY/MM/DD") & "#"

So it's all working fine.

Regards
Leonard Priestley

Leonard,

Your references to the controls on the form must be left out of the quotes,
otherwise VBA doesn't treat them as such. Try changing your strSQL to:

strSQL = "SELECT [PersonID], [DateOfInterest] FROM tblShiftTime"
strSQL = strSQL & " WHERE [PersonID] = '" & Me.txtPersonID & "'"
strSQL = strSQL & " AND [DateOfInterest] >=" & Me.txtStartDate
strSQL = strSQL & " AND [DateOfInterest] <=" & Me.txtEndDate

where I have assumed that PersonID is text. In case it is numeric, change
the second line to:
strSQL = strSQL & " WHERE [PersonID] = " & Me.txtPersonID

Also, keep in mind that in VBA all dates are US format (month/day/year), so
if your date fields are formatted differently you may need to make some
changes there to get it to filter correctly.

HTH,
Nikos

I am trying to restrict a recordset to those records which concern a
particular person, and which lie between two dates. The start and finish
dates are specified in two textboxes - txtStartDate and txtEndDate - and the
PersonID is taken from a textbox, txtPersonID, which is bound to the field
[PersonID] in a table.

So far I have this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [PersonID], [DateOfInterest] FROM tblShiftTime " & _
"WHERE [PersonID] = Me.txtPersonID AND [DateOfInterest] >= Me.txtStartDate
" & _
"AND [DateOfInterest] <= Me.txtEndDate"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveFirst

When I try to run this, I get the error message "Too few parameters:
Expected 3"
This seems to be referring to the three fields specified in the WHERE
statement. If I comment the
WHERE statement out, I do not get an error message, and if I reduce the
number of fields referred to,
the error message changes the number of expected parameters accordingly.
The problem arises at the
line: Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
At the time I run the code, all three textboxes referred to have sensible
data in them.

Any ideas anyone?

Leonard Priestley