dsoderstrom
asked on
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.POLineSta
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.
SELECT dbo_FS_POLine.POLineStatus
FROM dbo_FS_POLine
WHERE (((dbo_FS_POLine.POLineSta
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.
PatHartman
Based on what I can see, the query is correct. Can you post the query and the table with a little data?
Let's de-cruft it
Try
SELECT POLineStatus
FROM dbo_FS_POLine
WHERE POLineStatus="4"
Does this yield the correct result?
Try
SELECT POLineStatus
FROM dbo_FS_POLine
WHERE POLineStatus="4"
Does this yield the correct result?
ASKER
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
FROM dbo_FS_POLine
WHERE (((dbo_FS_POLine.POLineSta
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.
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
FROM dbo_FS_POLine
WHERE (((dbo_FS_POLine.POLineSta
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.
Occasionally, queries become corrupted. Compact and repair and cross your fingers.
@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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
ASKER
was the solution that worked