Query Question - Recognizing Upper & Lower Case in Results

[dbain30]

I have a table with a list of customer names. The problem is that when
the information was converted it capitalized the first letter of every
word. This is great for 99% of the table however it brought over a lot
of doctors with John Smith Md when it should be John Smith MD.

We have gone through and made a lot of corrections however I'd like to
run a query that would look at the capitalization and include / exclude
based on that. Currently I have the following: Like "*Md*" as my
criteria and it's pulling in both.

Also, is there a way to have Access systematically correct this using
an Update Query? The problem is that because the names vary in length
it's impossible to signify it's exact placement in the field so it
would have to include some type of operator.

Thanks in advance for any help!

 

[Guest]

We have gone through and made a lot of corrections however I'd like to
run a query that would look at the capitalization and include / exclude
based on that. Currently I have the following: Like "*Md*" as my
criteria and it's pulling in both.

Access queries are inherently non-case sensitive, unfortunately. However,
the InStr() function has an optional parameter that solves the problem:

LIKE "* MD*" And InStr([namefield], " Md", 0) > 0

will find records containing a blank followed by Md (you wouldn't want to
correct Dr. Camden to Dr. CaMDen after all!)

You can use an Update query such as

UPDATE namefield
SET namefield = Left([namefield], InStr([namefield], " Md", 0) -1) & " MD" &
Mid([namefield], InStr([namefield], " Md", 0) + 3)

to splice together the full name with the MD properly capitalized.

 

[Guest]

Here is an article which discusses Case Sensitive methods in Access queries:

How To Perform a Case-Sensitive JOIN Through Microsoft Jet
http://support.microsoft.com/?kbid=244693


Here is an chapter from John Viescas, demonstrating use of
a User Defined Function to correct name capitalisation:

http://msdn.microsoft.com/library/d.../OfficeAccessBuildingApplicationsCh5_Book.asp

(david)