Why is RecordCount is 1 if no rcds meet SQL stmt's criteria?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Hi,

I'm on Access 2003 on XP Pro w sp3.

I have the following SQL statement:

Set dbCur = CurrentDb
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrizeNo"
strSQL = strSQL & " FROM qrytblMailingList"
strSQL = strSQL & " WHERE (((MemType) = 'MT-D')) Or (((MemberTerm) =
'MTR-E'))"
Set rstNC = dbCur.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

It is working fine. I know this because I copied the actual SQL statement
and pasted it into the Design New Query and ran it and it works fine.

The only issues is if there is no records that meet the criteria the SQL
statement still returns one record that is NULL. If there is a record that
meets the criteria, the SQL statement also returns one record that has a
value in the NewDoorPrize No field.

In both cases (fails or works) rstNC.RecordCount is equal to 1. I would
have expected 0 if it failed and 1 if it worked? To determine if I have a
record, I'm testing "IsNull(rstNC!NewDoorPrizeNo)" instead of
newNC.RecordCount.


Why does it return a null record is the SQL failed?
 
Dennis said:
Hi,

I'm on Access 2003 on XP Pro w sp3.

I have the following SQL statement:

Set dbCur = CurrentDb
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrizeNo"
strSQL = strSQL & " FROM qrytblMailingList"
strSQL = strSQL & " WHERE (((MemType) = 'MT-D')) Or (((MemberTerm) =
'MTR-E'))"
Set rstNC = dbCur.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

It is working fine. I know this because I copied the actual SQL statement
and pasted it into the Design New Query and ran it and it works fine.

The only issues is if there is no records that meet the criteria the SQL
statement still returns one record that is NULL. If there is a record that
meets the criteria, the SQL statement also returns one record that has a
value in the NewDoorPrize No field.

In both cases (fails or works) rstNC.RecordCount is equal to 1. I would
have expected 0 if it failed and 1 if it worked? To determine if I have a
record, I'm testing "IsNull(rstNC!NewDoorPrizeNo)" instead of
newNC.RecordCount.


Why does it return a null record is the SQL failed?
Click to expand...

The SQL didn't fail. It returned exactly what the SQL statement asked
it to. This is how all of the aggregating methods work. You always get
a row back when using count(), max(), min(), sum() etc..

For what you want try...

Select TOP 1 DoorPrizeNo AS NewDoorPrizeNo
FROM qrytblMailingList
WHERE MemType = 'MT-D'
Or MemberTerm >= 'MTR-E'
ORDER BY DoorPrizeNo
 
Dennis said:
I'm on Access 2003 on XP Pro w sp3.

I have the following SQL statement:

Set dbCur = CurrentDb
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrizeNo"
strSQL = strSQL & " FROM qrytblMailingList"
strSQL = strSQL & " WHERE (((MemType) = 'MT-D')) Or (((MemberTerm) =
'MTR-E'))"
Set rstNC = dbCur.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

It is working fine. I know this because I copied the actual SQL statement
and pasted it into the Design New Query and ran it and it works fine.

The only issues is if there is no records that meet the criteria the SQL
statement still returns one record that is NULL. If there is a record that
meets the criteria, the SQL statement also returns one record that has a
value in the NewDoorPrize No field.

In both cases (fails or works) rstNC.RecordCount is equal to 1. I would
have expected 0 if it failed and 1 if it worked? To determine if I have a
record, I'm testing "IsNull(rstNC!NewDoorPrizeNo)" instead of
newNC.RecordCount.

Why does it return a null record is the SQL failed?
Click to expand...


That SQL can not fail. It is supposed to always return one
record with the Min value of the selected records or Null if
there are no records for Min to operate on. It will also
return Null if all of the selected records have Null in the
field.

Your test for Null is the right way to check the result..
 
Marsh,

Once again I want to say thanks for your assitance. I'm so thankful to you
and the others who answer questions on this forum.
 
Back
Top