Query wrongly SELECTs last row in table

[code green]

When I run this query

SELECT * FROM [OrderDetail] 
WHERE OrderDetail.[OrderNumber] = 244791

I get the required data along with an extra row.
This extra row is the last in the table and is not a true data entry, nor do I know where it came from.
The OrderNumber is actually empty but there is data in some of the fields.
Why does the query pick this record up?
I also tried

SELECT * FROM [OrderDetail] 
WHERE OrderDetail.[OrderSequenceNumber] = 244791
AND OrderSequenceNumber IS NOT NULL;

with the same result.
It is a 2003 database and I am looking at it in 2007.

[djdaveg]

Can youpost a screen grab of the result with the extra line?

[ChipR]

Is there a difference between OrderNumber and OrderSequenceNu mber here?

[code green]

No. But I can see the row when I view the table. The asterisk is in the leftmost margin.
I can SELECT but not COUNT this row, nor DELETE because it is not a record, it is the next empty row.
Don't understand why a query returns this row.

This row is also being returned intermitently in a script using ODBC which is the real problem.

[djdaveg]

Ah Ha, I think its is because you are using select *, try putting each individual field into the query, maybe that will work. If you need to know how to do this let me know.

Thanks
Dave

[code green]

Interesting idea, but sorry that didn't solve it djdaveg.
I would have been more confused if it had.

Just to reiterate, say for example there are four order lines for an order referenced by OrderNumber, the four lines are returned along with the next default row in the table.
This happens whether * fields are selected or just named fields.

SELECT Version, ProgramVersion, OrderSequenceNumber
FROM OrderDetail
WHERE (((OrderDetail.OrderNumber)=245388));

It happens everytime in Query Design - Datasheet View in Access 2007
looking like this

   Version	ProgramVersion	OrderSequenceNumber
    11	    HCFA	                   245388	
    11	    HCFA	                   245388	
    11	    HCFA	                   245388	
    11	    HCFA	                   245388
*    1        6AAA

But the problem is intermittent via ODBC.
In the meantime I will have to filter out these 'ghost' rows directly in the script.

[TheSmileyCoder]

Im gonna guess that the last row is a NewRecord where Version=1 is a default value, and 6AAA is also a default value.

That is why it doesn't show in your Count for instance, cause its not there yet.

[code green]

Hello TheSmileyOne.
So when I run a query in Query design, should this new record appear in the results datasheet view when it does not meet the WHERE criteria?

[TheSmileyCoder]

Yes it will also show there. Thats also what the asterisk is telling you, that its a new record(Well a POSSIBILITY of a new record.)

If your using the query in a form, you can set the forms AllowAdditions property to false, that will "hide" the row.

The reasons why it sometimes is not there, is that if the query has complicated joins, it might not be possible to have an "updatable" query, and in that case access will not show it to you.

[code green]

Yes it will also show there

Seems I have wasted a few peoples post with that. Sorry.

If your using the query in a form

I am using the query from a script via OBDC that writes orderlines to XML.
But recently it started returning empty orderlines and writing these to XML.
So I investigated by using the query directly in Access and noticed this spurious row.
I assumed maybe wrongly that was the problem.
Anyway I have amended the code to report the problem then skip the empty data.
Still a little mystified but thanks.