We help IT Professionals succeed at work.

Access 2007 Query returns incorrect reults

Jamist
Jamist asked
on
I have a simple query that selects all records from another query where 'Job Type'="P*".  When I only list one field everything works fine.  When I add additional fields (Columns) some 'Job Type'="LI" records appear.

Although I normally work in design view, I have shown the working and faulty sql below:

Works:
SELECT WO_All.WO_TYPE
FROM WO_All
WHERE (((WO_All.WO_TYPE) Like "p*"));


Faulty:
SELECT WO_All.WO_TYPE, WO_All.WO_STATUS, WO_All.REQST_DATETIME, WO_All.LAST_UPDATE
FROM WO_All
WHERE (((WO_All.WO_TYPE) Like "p*"));

I have used access for years and have never had this happen before.
Thanks in advance!
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Guessing that there is 'something else' affecting this.  Can you post a sample database?
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Does tacking the critera on to the original WO_All query (so that the select is done in one query versus a query based on a query) make any difference?

WHERE WO_All.WO_TYPE Like "p*";
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Btw, in one part of your question, you are saying 'Job Type'="P*".

Your query criteria, however is different: WHERE WO_All.WO_TYPE Like "p*";

Is the discrepancy a typo?  If not, that could be the problem.

Commented:
i dont find anything wrong with your query but still try same query using "%" tag

Say for example..

SELECT WO_All.WO_TYPE, WO_All.WO_STATUS, WO_All.REQST_DATETIME, WO_All.LAST_UPDATE
FROM WO_All
WHERE (WO_All.WO_TYPE Like "p*%");


Hope this helps to you
Programmer
Commented:
Hi,

I believe you have a corrupt database and compact and repair may not help.  

Create a new database and import all objects from the existing database.

That should do it.

Bill
Luke ChungPresident
BRONZE EXPERT

Commented:
Is there an index on the WO_Type field? That may be corrupt.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.