DAO Recordset Problem

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 -

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.

re: DAO Recordset Problem

What happens if you use:
Reading Database.OpenRecordset Method, I think it says a query should default to open as dynaset type, but you could also try:
If this doesn't work, then I can only guess that qry_EmailList is spelled wrong somewhere.

re: DAO Recordset Problem

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.

...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.

re: DAO Recordset Problem

I don't get an error in VBA from, for example:
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.

re: DAO Recordset Problem

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 -

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.

re: DAO Recordset Problem

At first glance, I don't see why
would result in anything different from
As you are just adding a constant which won't be visible in the results.

re: DAO Recordset Problem

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:

re: DAO Recordset Problem

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 -

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.

re: DAO Recordset Problem

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

re: DAO Recordset Problem

Ultimately got it working using this -

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.

re: DAO Recordset Problem

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.

re: DAO Recordset Problem

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.