Select, update query--same query, different result

[Paul]

Hi
I have an existing Update query that I just copied and converted to a Select
query. When I run it as an Update query, I get 4 records. However, when I run
it as a Select query, I get 411 records. It is the same query with the
exception of Select vs. Update.

Anyone know why this is?

Paul

 

[KARL DEWEY]

Why did you not post the SQL so it could be looked at?

 

[Ron2006]

You need to post BOTH of the queries....

 

[Paul]

Sorry:

SELECT query:

SELECT CDRC_AUTOMATION_CASES.TYPECODE, CDRC_AUTOMATION_CASES.NATURECODE,
CDRC_AUTOMATION_CASES.AMT_AWARDED, CDRC_AUTOMATION_CASES.CASENUM,
CDRC_AUTOMATION_CASES.PROGNUM, CDRC_AUTOMATION_CASES.FINALDISPDATE
FROM CDRC_AUTOMATION_CASES
WHERE (((CDRC_AUTOMATION_CASES.TYPECODE)<>"mis" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"Fel" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"JUV" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"CHd" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"SPE" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"FCC" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"MAt" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"CLC") AND
((CDRC_AUTOMATION_CASES.NATURECODE)<>"LEM") AND
((CDRC_AUTOMATION_CASES.AMT_AWARDED)>5000));

Update query:

UPDATE CDRC_AUTOMATION_CASES SET CDRC_AUTOMATION_CASES.TYPECODE = "CLC"
WHERE (((CDRC_AUTOMATION_CASES.TYPECODE)<>"mis" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"Fel" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"JUV" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"CHd" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"SPE" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"FCC" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"MAt" And
(CDRC_AUTOMATION_CASES.TYPECODE)<>"CLC") AND
((CDRC_AUTOMATION_CASES.NATURECODE)<>"LEM") AND
((CDRC_AUTOMATION_CASES.AMT_AWARDED)>5000));

 

[KARL DEWEY]

I do not see why but did you analyze the 'TYPECODE' field results?

 

[Paul]

Both are posted.

 

[Paul]

Actually, I found the problem. In both of the queries, some of the quoted
'where' values are lowercase, but are uppcase in the DB. Apparently, this
impacts the Select query, but not the update query.

Paul

 

[John W. Vinson]

Actually, I found the problem. In both of the queries, some of the quoted
'where' values are lowercase, but are uppcase in the DB. Apparently, this
impacts the Select query, but not the update query.

Yipe!!! It shouldn't. Access queries (SELECT or UPDATE) are case-insensitive.
Is this a native Access table, or linked to some other backend?