Selection criteria not working in MS Access Query.

I am trying to run the following select query in Access 2010.

SELECT dbo_FS_POLine.POLineStatus
FROM dbo_FS_POLine
WHERE (((dbo_FS_POLine.POLineStatus)="4"));

When I run the query I get records where POLineStatus field equals "4" or "5".  I should only be getting records where this field equals "4".  The POLineStatus is a text field with a length of 1.
There are other records in the table with  line statuses of "6", "2" and "3".  These are excluded from the results.  Why are the Status "5" records not being excluded?

The table dbo_FS_POLine is a linked SQL Server table.
dsoderstromAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Based on what I can see, the query is correct.  Can you post the query and the table with a little data?
0
Nick67Commented:
Let's de-cruft it
Try

SELECT POLineStatus
 FROM dbo_FS_POLine
 WHERE POLineStatus="4"

Does this yield the correct result?
0
dsoderstromAuthor Commented:
Nick67

I copied and pasted your code into the query.  same results.

Just as a test, I modified the query as shown below to also display the ASCII character code for the POLineStatus value.

SELECT dbo_FS_POLine.POLineStatus, Asc([POLineStatus]) AS Expr1
FROM dbo_FS_POLine
WHERE (((dbo_FS_POLine.POLineStatus)="4"));

the query now displays a value of  52 for the status 4 records and 53 for the status 5 records.  So, the status values are definitely different.  Questions is why the selection criteria in the query does not work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Occasionally, queries become corrupted.  Compact and repair and cross your fingers.
0
Nick67Commented:
@PatHartman's suggestion has merit

Then let's try

SELECT POLineStatus
  FROM dbo_FS_POLine
  WHERE Asc([POLineStatus]) = 52

dbo_ suggests SQL Server
What is the backend, and do you have access to it through SSMS or similar tools?
0
dsoderstromAuthor Commented:
Okay, I did the following:

1) compacted and repaired the database.  Same problem
2) decompiled the database. Same problem.
3) Rewrote the query using the ascii character code as the selection criteria as suggested by Nick67 above.  Same problem.

Finally, I created a new blank Access database and copied In all objects from the database having the problem.  I recreated the query in this new database and it worked.  Evidently the old database was corrupted in some manner that was not fixable by compacting, repairing and decompiling.  Kind of a strange thing that I have never had happen before.

Anyway, thank you both for your suggestions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
Weird one.
If you still have the old file, I wonder if deleting/relinking dbo_FS_POLine would have been a sufficient trick?
That's a little less nuclear that the full export/import
0
dsoderstromAuthor Commented:
forgot to mention that I did try deleting the file and relinking the table in the old database.  Still had the same problem after doing that.
0
dsoderstromAuthor Commented:
was the solution that worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.