DAO Recordset Problem

[Monroeski]

I'm trying to populate a recordset with a query I have stored in Access 07. I basically copied the lines from the help file on how to do this but substituted my own info -

Dim dbsCurrent As DAO.Database
Dim rstEmail As DAO.Recordset
Dim strService As String
 
 
Set dbsCurrent = CurrentDb
Set rstEmail = dbsCurrent.OpenRecordset("qry_EmailList")

But every time I try this, I get a "too few parameters: Expected 1" error on that last line. It works perfectly fine if I put the name of a table in there, and lets me move through the recordset and print data and everything, but for some reason it doesn't work with queries.

I must just be missing something extremely obvious here, but I've been stumped for a while.

[ChipR]

What happens if you use:

Set rstEmail = dbsCurrent.OpenRecordset("SELECT * FROM qry_EmailList") 

Reading Database.OpenRecordset Method, I think it says a query should default to open as dynaset type, but you could also try:

Set rstEmail = dbsCurrent.OpenRecordset("qry_EmailList", 2) 'dbOpenDynaset

If this doesn't work, then I can only guess that qry_EmailList is spelled wrong somewhere.

[Monroeski]

Didn't work, but I think I may have figured it out. The query itself has references to controls on a specific form. Edited that out, but I'm running into another problem with type mismatches that I'm assuming is related to the fact that the query is adding and subtracting a date field.

[DateRevised] + 1095 - Date

...works fine in Access, but I'm assuming VBA has a problem with it even if it is just pulling it from an existing query.

[ChipR]

I don't get an error in VBA from, for example:

?#1/1/01# + #2/2/02# + 2500
12/10/2109

So I wouldn't expect that line to produce an error, even if it's not giving you a correct date. We may need a little more information. It will be helpful if you can track down the problem, since assumptions can lead us to wasting a lot of time. NeoPa wrote a nice guide on Debugging in VBA that will certainly be helpful if you don't already use it.

[Monroeski]

Whenever I hit the button to run the code, I get a type mismatch error, and when I hit debug it always highlights the last ampersand in this code -

'Set rstEmail = "SELECT tbl_PolicyList.Service, tbl_PolicyList.PolicyNumber, tbl_PolicyList.Title, [DateRevised]+1095 AS TestField, [DateRevised]+1095-Date() AS DateDue, tbl_Contacts.FirstContact, tbl_Contacts.SecondContact " & _
    "FROM tbl_Contacts INNER JOIN tbl_PolicyList ON tbl_Contacts.Service = tbl_PolicyList.Service " & _
    "WHERE ((([DateRevised] + 1095 - Date) <= " & strWhereDate & ")) " & _
    "ORDER BY [DateRevised]+1095-Date();"

I'm assuming the problem has to do with how I am adding and subtracting dates. Is just writing "+ 1095" wrong, and if so, what format would I use instead?

Will start looking through that guide.

[ChipR]

At first glance, I don't see why

ORDER BY [DateRevised] + 1095 - Date()

would result in anything different from

ORDER BY [DateReviesd]

As you are just adding a constant which won't be visible in the results.

[ChipR]

Oh, the actual problem is probably in the previous line where you compare a Date type to a String type. To tell Access you have a date, try changing that line to:

  "WHERE ([DateRevised] + 1095 - Date) <= #" & strWhereDate & "# " & _ 

[Monroeski]

Still didn't work.

I tried simplifying down and isolating different parts of the query, and now even this is giving me a type mismatch error -

Set rstEmail = "SELECT tbl_PolicyList.Service, tbl_PolicyList.ServiceChief, tbl_PolicyList.PolicyNumber, tbl_PolicyList.Title, tbl_PolicyList.DateIssued, tbl_PolicyList.DateRevised, tbl_PolicyList.PolicyReviewer FROM tbl_PolicyList ORDER BY tbl_PolicyList.Service;"

And there is no calculation going on at all in there. I tried removing the semicolon at the end and I tried getting rid of the "tbl_PolicyList." before every field and neither one worked.

[ChipR]

It looks like you lost part of the line. Set recordset = String does not compute.

Set rstEmail = dbsCurrent.OpenRecordset("SELECT...")

[Monroeski]

Ultimately got it working using this -

strSQL = "SELECT tbl_PolicyList.Service, tbl_PolicyList.PolicyNumber, tbl_PolicyList.Title, [DateRevised]+1095 AS TestField, [DateRevised]+1095-Date() AS DateDue, tbl_Contacts.FirstContact, tbl_Contacts.SecondContact " & _
    "FROM tbl_Contacts INNER JOIN tbl_PolicyList ON tbl_Contacts.Service = tbl_PolicyList.Service " & _
    "WHERE ([DateRevised] + 1095 - Date()) <= " & intWhereDate & " " & _
    "ORDER BY [DateRevised]+1095;"
Set rstEmail = dbsCurrent.OpenRecordset(strSQL)

Note I changed "strWhereDate" to "intWhereDate" because it was a number from the beginning anyway that I just mistakenly named str and never fixed.

Somewhere along the line I mistakenly deleted the dbsCurrent.OpenRecordset part and accidentally started trying to load the string straight into the recordset (see post 5) which apparently doesn't work.. There goes a good chunk of time I'll never get back.

[ChipR]

Glad you got it working. Things like that can be really hard to spot, so don't be too upset until you spend days looking for some little typo.

[NeoPa]

A tip when debugging SQL strings, is to print the string itself before trying to use it. MsgBox() or Debug.Print can be used for this. I've found that one invaluable over the years. The number of times it becomes clear that what you wanted to appear in the string and to get processed by the SQL engine, is quite different from what actually ended up there, is just too many to count.

PS. I changed the thread title for you. It's important that the title bears a meaning as we have to deal with a large number of threads here and if there's nothing to say what it is other than it's a question (or even a dumb question) then it doesn't help.