NOT CASE SENSITIVE

  • Thread starter Thread starter CAROL
  • Start date Start date
C

CAROL

could you pls look over this query sql and let me know
what i need to add so that the left=left will read both
upper and lower case in the comparison. the field in one
one table is upper and lower case whereas the similar
field in the other table is all upper. thought i could
accomplish this in the query without having to go into the
tables and make case adjustments on the data. you guys
are great with the support. thx!

UPDATE [vendor mailing] LEFT JOIN VENDORS ON [vendor
mailing].vendor = VENDORS.VENDOR SET [vendor mailing].
[vendor #] = [vendors].[VENDOR#]
WHERE ((Left([VENDOR MAILING].[VENDOR],9)=Left([VENDORS].
[VENDOR],9)));
 
access is not case sensitive when comparing values, but you can use ucase
function for the field that has mixed case and then compare
UCASE(Field1)= FIELD1
 
could you pls look over this query sql and let me know
what i need to add so that the left=left will read both
upper and lower case in the comparison.
Click to expand...

If it's an Access .mdb file that contains the tables, it's already
case insensitive (in fact it's rather hard to force it to be case
sensitive!).
 
ok, thx for all the feedback. so now, i must have another
problem since my query is not returning the data.

the fields that i am comparing in two different tables,
are text, same length. here's an example of what i'm
dealing with...


table a. company = Artwork Limited
table b. company = Artwork LTD.

and so on and so forth with about 150 records. that's why
i chose to compare with left(length=9). maybe i should
go another route? thx.
 
could you pls look over this query sql and let me know
what i need to add so that the left=left will read both
upper and lower case in the comparison. the field in one
one table is upper and lower case whereas the similar
field in the other table is all upper. thought i could
accomplish this in the query without having to go into the
tables and make case adjustments on the data. you guys
are great with the support. thx!

UPDATE [vendor mailing] LEFT JOIN VENDORS ON [vendor
mailing].vendor = VENDORS.VENDOR SET [vendor mailing].
[vendor #] = [vendors].[VENDOR#]
WHERE ((Left([VENDOR MAILING].[VENDOR],9)=Left([VENDORS].
[VENDOR],9)));
Click to expand...

You're joining VENDORS to VENDOR MAILING *twice*: once in the WHERE
clause using Left(), and again in the LEFT JOIN clause - you're
insisting that the VENDOR fields be equal.

If you don't have any field in the VENDOR MAILING table which can -
reliably! - be used as a link to the VENDORS table, you won't be able
to get an updateable query! You could *try*

LEFT JOIN VENDORS
ON Left([Vendor Mailing].[Vendor], 9) = Left([Vendors].[Vendor], 9)

but a) it may not let you join on a function call and b) it probably
will NOT let you update the resulting query since it may not be a
unique join (e.g. vendors named ABERCROMBIE LTD. London and
ABERCROMBIE Corp. USA).

Getting clean joins on dirty data is *not* easy!
 
AHA, Try changing your join statement to use the left function. This type of
query join cannot be done in the Query grid, but must be done in SQL.

UPDATE [vendor mailing]
LEFT JOIN VENDORS
ON LEFT([vendor mailing].vendor,9) = Left(VENDORS.VENDOR,9)
SET [vendor mailing].[vendor #] = [vendors].[VENDOR#]

Your original query was only going to match if the two vendor fields were identical.

ok, thx for all the feedback. so now, i must have another
problem since my query is not returning the data.

the fields that i am comparing in two different tables,
are text, same length. here's an example of what i'm
dealing with...

table a. company = Artwork Limited
table b. company = Artwork LTD.

and so on and so forth with about 150 records. that's why
i chose to compare with left(length=9). maybe i should
go another route? thx.
-----Original Message-----
could you pls look over this query sql and let me know
what i need to add so that the left=left will read both
upper and lower case in the comparison. the field in one
one table is upper and lower case whereas the similar
field in the other table is all upper. thought i could
accomplish this in the query without having to go into the
tables and make case adjustments on the data. you guys
are great with the support. thx!

UPDATE [vendor mailing] LEFT JOIN VENDORS ON [vendor
mailing].vendor = VENDORS.VENDOR SET [vendor mailing].
[vendor #] = [vendors].[VENDOR#]
WHERE ((Left([VENDOR MAILING].[VENDOR],9)=Left([VENDORS].
[VENDOR],9)));

.
Click to expand...
Click to expand...
 
Back
Top