Preserving Case In Update Query

[PeteCresswell]

Got a bunch of things called Traunches.

Traunches have "Ratings".

Rating "AAA" is different from "Aaa".

We want to update all ratings by joining the tblTraunche to a
spreadsheet on a common key.

So far, so good.... and most of the ratings get updated.


But it seems like MS Access (JET?) does not recognize that "AAA" is
different from "Aaa", and leaves such a rating unchanged.


Anybody been here?

 

[Tom van Stiphout]

On Tue, 7 Apr 2009 06:26:07 -0700 (PDT), PeteCresswell

Use what's called a "surrogate key": add a RatingID (autonumber) to
the table, and join on that.
Note that you will have other challenges because indeed Jet is
essentially case insensitive. There is a topic in the help file "Sort
records in case-sensitive order" you should be aware of.
SQL Server has the option to be installed case-sensitive (binary sort
order).

-Tom.
Microsoft Access MVP

 

[John Spencer MVP]

Can you post the SQL of the UPDATE query?

You can use VBA functions to compare two values
StrComp("AAA","Aaa",0) returns zero if the two strings are identical.

Instr(1,"AAA","Aaa",0) returns zero if the two string are NOT identical.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[(PeteCresswell)]

Per John Spencer MVP:

Can you post the SQL of the UPDATE query?

You can use VBA functions to compare two values
StrComp("AAA","Aaa",0) returns zero if the two strings are identical.

Instr(1,"AAA","Aaa",0) returns zero if the two string are NOT identical.

I did an end run.

Found out user's basic desire was just to see all the tranches
and their ratings in a single window (which was his spreadsheet)
as opposed to having to drill down into the details of each
tranche.

So I just concocted a screen that listed all the tranches with a
dropdown of each one's ratings.

FWIW, the problem SQL:
====================================================
UPDATE [$ttblRatingImport_MikesOneTime] INNER JOIN tblTranche ON
[$ttblRatingImport_MikesOneTime].TrancheID = tblTranche.TrancheID
SET tblTranche.RatingMoodyLong =
[$ttblRatingImport_MikesOneTime].[RatingMoodyLong],
tblTranche.RatingSpLong =
[$ttblRatingImport_MikesOneTime].[RatingSpLong];
====================================================

Sounds like Instr() could be used somehow to get around the
problem, but I'm not imaginative enough to figure out how.

 

[John Spencer]

Possibly something like the following


UPDATE [$ttblRatingImport_MikesOneTime] INNER JOIN tblTranche ON
[$ttblRatingImport_MikesOneTime].TrancheID = tblTranche.TrancheID
SET tblTranche.RatingMoodyLong =
[$ttblRatingImport_MikesOneTime].[RatingMoodyLong],
tblTranche.RatingSpLong =
[$ttblRatingImport_MikesOneTime].[RatingSpLong]
WHERE
StrComp(tblTrance.RatingMoodyLong,[$ttblRatingImport_MikesOneTime].[RatingMoodyLong]
,0) <> 0
OR
strComp(tblTranche.RatingSpLong,[$ttblRatingImport_MikesOneTime].[RatingSpLong],0)<>0

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Per John Spencer MVP:

Can you post the SQL of the UPDATE query?

You can use VBA functions to compare two values
StrComp("AAA","Aaa",0) returns zero if the two strings are identical.

Instr(1,"AAA","Aaa",0) returns zero if the two string are NOT identical.

I did an end run.

Found out user's basic desire was just to see all the tranches
and their ratings in a single window (which was his spreadsheet)
as opposed to having to drill down into the details of each
tranche.

So I just concocted a screen that listed all the tranches with a
dropdown of each one's ratings.

FWIW, the problem SQL:
====================================================
UPDATE [$ttblRatingImport_MikesOneTime] INNER JOIN tblTranche ON
[$ttblRatingImport_MikesOneTime].TrancheID = tblTranche.TrancheID
SET tblTranche.RatingMoodyLong =
[$ttblRatingImport_MikesOneTime].[RatingMoodyLong],
tblTranche.RatingSpLong =
[$ttblRatingImport_MikesOneTime].[RatingSpLong];
====================================================

Sounds like Instr() could be used somehow to get around the
problem, but I'm not imaginative enough to figure out how.