parameter query not working as expected

[Cory]

When I run the subroutine testEmailContacts the msgbox says that there
is only 1 record. The sql for qyEmailContactsQyCard is below. There is
over 3000 records in the table "tbl:Contact". What am i doing wrong?
Thanks in advance.

PARAMETERS cardYesNo Bit, ParSearchCard Bit;
SELECT [tbl:Contact].ContactID
FROM [tbl:Contact]
WHERE ((([tbl:Contact].Card)=[cardYesNo])) OR ((([ParSearchCard])=No));

Sub testEmailContacts()

Dim db As DAO.database
Set db = CurrentDb
Dim qdf As QueryDef
Set qdf = db.QueryDefs("qyEmailContactsQyCard")

qdf.Parameters("CardYesNo") = No 'Card
qdf.Parameters("ParSearchCard") = No 'SearchCard

MsgBox qdf.OpenRecordset.RecordCount

Set qdf = Nothing
Set db = Nothing

End Sub

[Tony Toews]

Cory <[e-mail]> wrote:

When I run the subroutine testEmailContacts the msgbox says that there
is only 1 record. The sql for qyEmailContactsQyCard is below. There is
over 3000 records in the table "tbl:Contact". What am i doing wrong?

As an alternative dump the parameter query and use Where clauses built in code
instead.

One method of using SQL queries in code as parameter queries can be difficult to work
with in code
http://www.granite.ab.ca/access/sqlqueries.htm

The problem is that you need to use a query in code such as looping through a
recordset or running an action query of some sort but you want to limit the number of
records via some criteria.

However when you run the query in the query window you have to enter appropriate
parameter values. Locating an appropriate value can be a pain in the ...

Actually what I always do when action or recordset queries get the slightest bit ugly
I make a SELECT query with the required fields. By ugly I mean one join to another
table, a lot of fields or whatever. I get this query working nicely showing all the
records I want. But with no selection criteria. Now I can quickly see if the query
looks proper now or six months from now when I forget what appropriate values were
for the criteria. I save that query.

I then create the action or SELECT query in the QBE grid with dummy selection
criteria based on that query I just created. I do NOT save it. Instead I click to the
SQL code view and copy the SQL code to the clipboard. Now I go into my VBA module
and paste the SQL code into the VBA code.. I then setup the strings with the double
quotes and line continuation for readability. I also setup the proper WHERE clause
criteria from my calling form or my code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm