Prev: Average time from date column?
Next: sum a count column in query
From: Paul on 12 Apr 2010 14:01
Both are posted.

"Ron2006" wrote:

> You need to post BOTH of the queries....
> .
>
From: Paul on 12 Apr 2010 15:40
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


"KARL DEWEY" wrote:

> I do not see why but did you analyze the 'TYPECODE' field results?
>
> --
> Build a little, test a little.
>
>
> "Paul" wrote:
>
> > 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" wrote:
> >
> > > Why did you not post the SQL so it could be looked at?
> > >
> > > --
> > > Build a little, test a little.
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > 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
From: John W. Vinson on 12 Apr 2010 16:50
On Mon, 12 Apr 2010 12:40:01 -0700, Paul <Paul(a)discussions.microsoft.com>
wrote:

>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?
--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: Average time from date column?
Next: sum a count column in query