SQL SELECT returns too many rows

[dogdays]

Ms Access 2003 win7 32 bit
I have the following SQL statement

SELECT nIdentity FROM tblPayments WHERE (nHouseholdNumber = 12683 AND nForYear = 2011)

There are eight rows that have household number 12683, only one of the of the eight rows has year 2011. Yet the query returns eight rows.
Advice appreciated.

tia Jack

 

[Norie]

How are you storing the year?

What do the other records have for a value in that field?

 

[dogdays]

nHouseholdNumber is a long integer, nForYear is an integer.
nForYear has the following values:
2005
2006
2007
2008
2009
2009
2010
2011

Jack

 

[Joe4]

Are those eight nForYear values the values you are having returned?

It would seem to suggest that you may have a typo in your statement and maybe mis-typed the field name in your where clause. I would double-check that.

 

[dogdays]

Problem solved. I tested for a record count <> 0, but neglected to movelast movefirst. When I did that the record count dropped to 1.

thanks for the advice

 

[Norie]

I thought this was a query not a recordset.

Getting a row count for a recordset can sometimes be tricky.

 

[Joe4]

I thought this was a query not a recordset.

Me too!
That is critical information to know, and changes the problem completely.

 

[dogdays]

Norie:
My fault, next time I will be more explicit.

Thanks for the reply Jack

 

[Norie]

Jack

No probem.