By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,775 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,775 IT Pros & Developers. It's quick & easy.

Query wrongly SELECTs last row in table

code green
Expert 100+
P: 1,726
When I run this query
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [OrderDetail] 
  2. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [OrderDetail] 
  2. WHERE OrderDetail.[OrderSequenceNumber] = 244791
  3. AND OrderSequenceNumber IS NOT NULL;
with the same result.
It is a 2003 database and I am looking at it in 2007.
Jan 20 '10 #1
Share this Question
Share on Google+
9 Replies

P: 9
Can youpost a screen grab of the result with the extra line?
Jan 20 '10 #2

Expert 100+
P: 1,287
Is there a difference between OrderNumber and OrderSequenceNumber here?
Jan 20 '10 #3

code green
Expert 100+
P: 1,726
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.
Jan 20 '10 #4

P: 9
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.

Jan 20 '10 #5

code green
Expert 100+
P: 1,726
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Version, ProgramVersion, OrderSequenceNumber
  2. FROM OrderDetail
  3. WHERE (((OrderDetail.OrderNumber)=245388));
It happens everytime in Query Design - Datasheet View in Access 2007
looking like this
Expand|Select|Wrap|Line Numbers
  1.    Version    ProgramVersion    OrderSequenceNumber
  2.     11        HCFA                       245388    
  3.     11        HCFA                       245388    
  4.     11        HCFA                       245388    
  5.     11        HCFA                       245388
  6. *    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.
Jan 20 '10 #6

Expert Mod 100+
P: 2,321
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.
Jan 20 '10 #7

code green
Expert 100+
P: 1,726
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?
Jan 21 '10 #8

Expert Mod 100+
P: 2,321
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.
Jan 21 '10 #9

code green
Expert 100+
P: 1,726
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.
Jan 21 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.