Erroneous Return on SQL Statement

[LarryDeLaruelle]

I have an sql statement to select records from a subordinate table based on a match of record number and a no condition on a check box:

The assignment looks like this:

strSQL = "SELECT AppID, Scored FROM tblTests WHERE (((AppID)= '" & cboSelect & "') AND ((Scored)=No));"

The actual string looks like this:

SELECT AppID, Scored FROM tblTests WHERE (((AppID)= '2001008') AND ((Scored)=No));

I have two records in the table for this AppID. If I set the Scored to Yes for both, no records are found and the RecordCount is zero. However, if I uncheck one of the records (one only) the RecordCount then is two instead of one. If I uncheck both, the RecordCount is still two.

I have copied the string to a blank query and pasted it and it works fine -- returning the correct number of records based on what I check/uncheck.

This is driving me crazy. Any ideas on what is happening here and what I need to do to correct it?

Thanks.
Larry De Laruelle
larry1de@yahoo.com

 

[Databaseguy]

Have you tried using -1(Checked) and 0 (Unchecked) instead of "No" ? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[LarryDeLaruelle]

Tyrone:

Yep! Tried that; still get the same result.

It it possible that there is something flaky with the records themselves? I tried checking/unchecking each one with the same results.
Larry De Laruelle
larry1de@yahoo.com

 

[Databaseguy]

Hmmmmmm....

It LOOKS like it should work.

Perhaps you should try opening the recordset as if you were opening a paramater query.

In your SQL statement:

PARAMETERS [AppID]Integer,[Scored] Boolean
SELECT......blah blah
Then when you open your recordset, force the parameters:

Dim qdef
Set qdef = ("yourQuery&quot
qdef![AppID] = cboSelect
qdef![Scored] = 0


Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[LarryDeLaruelle]

Tyrone:

Thanks for the assist. I solved the problem simply by adding an rsTemp.MoveLast command before the RecordCount test. Duh! I know better; should have caught it sooner.

Is there an advantage to using the Parameters and QDF?

I understand the Parameters declaration, but I'm not sure I get the QDF part. Where do you do the Set rsTemp = . . .

Or is that what you are doing with the query def?

Thanks again.
Larry De Laruelle
larry1de@yahoo.com

 

[Databaseguy]

Yeah, that's almost as bad as forgetting .movenext in a loop. (Something I've never done.....)

Setting the parameters is necessary when calling a crosstab from code, as is setting the column names. Otherwise, you get a run time error that makes no sense.

If you have a query that requires user input or gets it's parameter from a form (i.E. [forms].[yourform].[yourfield]),and you're calling it fronm code, Access will also complain if you don't set the parameters using the Qdef method.... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[LarryDeLaruelle]

Tyrone:

Funny you should mention missing the MoveNext in a loop. Choke, I did do that very thing today.

Thanks for your response. I'll keep the Parameters declaration in mind. I haven't gotten brave enough to try a cross tab in SQL but you never know.
Larry De Laruelle
larry1de@yahoo.com