Solved

MS Access 2010 search case senstive values within two columns

Posted on 2013-02-07
4
395 Views
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"
0
Comment
Question by:lynmke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 333 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

0
 
LVL 61

Accepted Solution

by:
mbizup earned 333 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.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 167 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

http://support.microsoft.com/kb/209674

you can run a query like this

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

Author Closing Comment

by:lynmke
ID: 38873588
Thanks, all solutions accepted.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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