Problem trying to open a recordset

[magicdds]

I keep getting an error message when trying to run the following code:

Dim rstDayFile As DAO.Recordset
Dim strSql As String
Dim dbs As DAO.Database
Dim mes As String

Set dbs = CurrentDb

strSql = "SELECT * FROM RecallQuery WHERE PatID > 0"
Set rstDayFile = dbs.OpenRecordset(strSql)

Do While rstDayFile.EOF = False
mes = rstDayFile.Fields(0)
MsgBox mes
rstDayFile.MoveNext
Loop

rstDayFile.Close


The error message is:

Run Time Error "3061"
Too few parameters. Expected 2

The error occurs when trying to execute
Set rstDayFile = dbs.OpenRecordset(strSql)

Can anyone tell me what I am doing wrong.

Thanks

 

[Allen Browne]

The error indicates that there are 2 names Access does not recognise.

Is there a query named RecallQuery?
Does it return a column named PatID?

If RecallQuery is actually a query (not a table), does this query contain
parameters? For example, if this query uses:
[Forms].[Form1].[Text0]
when you run the query, it calls the Expression Service (ES.) If Form1 is
open, the ES reads the value from there; if not, JET pops up a dialog asking
for the value. But the ES is not involved when you OpenRecordset, so JET
returns an error that the parameter was not supplied.

If that's what's going on, concatenate the value into the query string. You
may end up with something like this:
strSql = "SELECT Table1.* FROM Table1 WHERE SomeField = """ &
Forms!Form1!Text0 & """ ORDER BY SomeField;"

 

[Rui]

Hi,

It coudl be one of three things as the code itself is correct:

1. did you actually reference the DAO library?
2. is the database an mdb file or a project using SQL server - if so then
you cant use currentdb command
3. there is an error on the actual sql statement i.e. no such name as PatID

Rui

 

[magicdds]

It seems that the problem was in fact that I was trying to open the recordset
from data in the query. So instead, I am trying to get the data from the
table upon which the query was based. But now I am getting a problem with the
WHERE part of the SQL statement.

The code looks like this:

Dim rst As DAO.Recordset
Dim strSql As String
Dim dbs As DAO.Database
Dim mes As String

Set dbs = CurrentDb

strSql = "SELECT * from Patients WHERE RecallDate >= " &
Forms!Recall!FirstDate
Set rst = dbs.OpenRecordset(strSql)

mes = rst.Fields("recalldate")
MsgBox mes

rst.Close

strSql = "SELECT * from Patients WHERE RecallDate <= " & Forms!Recall!LastDate
Set rst = dbs.OpenRecordset(strSql)

mes = rst.Fields("recalldate")
MsgBox mes

rst.Close

On the Form named Recall,
FirstDate is 1/1/2000
LastDate is 2/7/2010

In the table Patients, the only record that has a RecallDate,
RecallDate is 6/1/2009

When I run the code, the first Message Box results 6/1/2009. When try to Set
rst the second time, I get an error that there are no records. If I change

strSql = "SELECT * from Patients WHERE RecallDate <= " & Forms!Recall!LastDate

to

strSql = "SELECT * from Patients WHERE RecallDate >= " & Forms!Recall!LastDate

Then it works fine, but 6/1/2009 is not greater than 2/7/2010.
In the query builder it seems to work correctly, but in the code it doesn't.
Any ideas on how to fix this?

Thanks
Mark

 

[Rui]

Personally, I think dates are probably one of the most difficult feature in
Access
I believe Access always stores date format in mm/dd/yyyy and you need to
take this into account when you code queries with dates in the where clause.

If I recall correctly, you may need to 'play' a bit with the format function
and put this between ##.

Try like this
strSql = "SELECT * from Patients WHERE RecallDate <= #" &
Format(Forms!Recall!LastDate, "mm/dd/yyyy") & "#"

hope this helps

 

[magicdds]

Allen and Rui --- Thanks for your help

Mark