MS Access 2010 search case senstive values within two columns

Posted on 2013-02-07
Medium Priority
Last Modified: 2013-02-10
My problem is that I have two columns, both with alphanumeric values. This two clumns have the same data but in different cases.

One Column may a field with Uppercase alphabets within the field say "12345DF2313tjm" this is compared to the other column's  value  "12345df2313tjm".

I have over 10,000 rows with similar case mismatch. How can I make access compare the two columns, then if there is a case mismatch, put a new value on a new column all in upper case. In this case the new coulmn would have "12345DF2313TJM"
Question by:lynmke
  • 2
LVL 61

Assisted Solution

mbizup earned 1332 total points
ID: 38863854
An easy way to do this - but not necessarily the most efficient is to simply uppercase your column:

UPDATE YourTable
SET YourField = UCase(YourField)

Open in new window

LVL 61

Accepted Solution

mbizup earned 1332 total points
ID: 38863859
Or if you are using a separate field:

UPDATE YourTable
SET YourNewField = UCase(YourOldField)

Open in new window

From your description, you don't really need a comparison, and uppercasing either field will give you your expected results.
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 668 total points
ID: 38863885
You can use the Asc() function or the StrComp() function to have Microsoft Access differentiate between case-sensitive text strings.
see this link

How to Return Case-Sensitive Matches in Queries


you can run a query like this

update tableName
set [newcolumn]=ucase([Column1])
where strcomp([column1],[column2],0) <>0

Author Closing Comment

ID: 38873588
Thanks, all solutions accepted.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question