[thelist] [access] transforming text

[david.landy at somerfield.co.uk]

Colin,

I'm not sure exactly what you want here, do you want to correct all existing
data in the table, or deal with it on-the-fly in your application? Or both?

1. To correct the existing field data, you can run a query in Access such
as:

UPDATE names SET firstname = UCase(Left(firstname, 1)) &
LCase(Mid(firstname, 2))

**** NB first BACK UP your table, as this is a non-reversible query **** 

If you can't run this query in Access -- where the built-in functions UCase,
LCase, and Mid are available -- you could also write code in ASP (or any
other language that has ODBC access to the database) to scan the table and
update each row individually. 

2. To correctly display wrongly formatted data in your application, ie to
display "John" where the data is "JOHN", it depends what language you are
developing in for the most appropriate strategy. As ususal, the more info
you give about what you are up to, the easier it is to assist you.

Hope this helps,

David.

-- 
David Landy, IT Consultant, Business Intelligence
Somerfield/KwikSave Support Centre
Whitchurch, Bristol, UK. Tel: 0117 301 8977
david.landy at somerfield.co.uk <mailto:david.landy at somerfield.co.uk>    
 
"...Our deepest fear is not that we are inadequate. Our deepest fear is
that we are powerful beyond measure." - From A RETURN TO LOVE by
Marianne Williamson © 1992	


-----Original Message-----
From: Colin Gremillion [mailto:gremilco_caz at yahoo.com]
Sent: Tuesday, 06 April 2004 17:17
To: thelist at lists.evolt.org
Subject: [thelist] [access] transforming text


Hi all,

I'm not too hot with databases, and I need to find a
solution to this issue.
There is an Access database with about 15 fields,
thousands of records. One of the fields is first name.
Some of the records in this field are in ALL CAPS,
others are Normal Type. I need something that
transforms the ALL CAPS to Normal Type automatically
within the table.
The user insists that this was done before by someone
else and that it was not done through a query (but I'm
doubting that after doing some research). Based on
research, I have several leads (macros, transforming,
adding sql statements to a query), but what would be
the best solution for this?

Gracias for any help,
colin, bocmaxima81_NOSPAM at yahoo.com
 
If you are not the intended recipient of this e-mail, please preserve the
confidentiality of it and advise the sender immediately of any error in
transmission. Any disclosure, copying, distribution or action taken, or
omitted to be taken, by an unauthorised recipient in reliance upon the
contents of this e-mail is prohibited. Somerfield cannot accept liability
for any damage which you may sustain as a result of software viruses so
please carry out your own virus checks before opening an attachment. In
replying to this e-mail you are granting the right for that reply to be
forwarded to any other individual within the business and also to be read by
others. Any views expressed by an individual within this message do not
necessarily reflect the views of Somerfield.  Somerfield reserves the right
to intercept, monitor and record communications for lawful business
purposes.