Select, update query--same query, different result

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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));
 
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
 
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.
Click to expand...

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