Convert to UpperLower

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have names/addresses that are either all upper, all lower or mixed,
especially names like van den STEEN. I need to be able to convert to the
proper upper/lower format.

I have tried the input mast >L<?????, but if there are more than 1 piece to
a name, it only converts the first piece (Van), leaving den STEEN as it. I
did at one point a month ago figured out how in an expression with in a
query, but for the life of me I can't find it (I will blame it on the
holidays) and I have not been able to figure it out again.

Any help is very much appreciated!

Thanks
Vivian
 
Hi Vivian,

You can use an update query to update the values to proper case. Create a
normal select query. Apply any appropriate criteria to filter records. Then,
in query design view, click on Query > Update Query. You should see a new row
in the QBE grid labelled "Update To:". Enter the following:

Update To: StrConv([FieldName],3)

where FieldName is the name of your field.

As with any action query, you should make a backup of your database first,
just in case you don't get the expected result. It's always easier to recover
from a backup than try to undo the results of an action (update, append,
delete) query.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have names/addresses that are either all upper, all lower or mixed,
especially names like van den STEEN. I need to be able to convert to the
proper upper/lower format.

I have tried the input mast >L<?????, but if there are more than 1 piece to
a name, it only converts the first piece (Van), leaving den STEEN as it. I
did at one point a month ago figured out how in an expression with in a
query, but for the life of me I can't find it (I will blame it on the
holidays) and I have not been able to figure it out again.

Any help is very much appreciated!

Thanks
Vivian
 
PS. I forgot to mention that the StrConv function will capitolize the first
letter of each word. Thus, "van den STEEN" will be converted to "Van Den
Steen", which may not be what you want. I suspect that you really want "Van
den Steen". You'll need to manually adjust such names.

A better alternative, especially if you are dealing with messy name data on
any kind of frequent basis, is to purchase a copy ($39 US) of Splitter for
Microsoft Access:

http://www.infoplan.com.au/splitter/


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Vivian,

You can use an update query to update the values to proper case. Create a
normal select query. Apply any appropriate criteria to filter records. Then,
in query design view, click on Query > Update Query. You should see a new row
in the QBE grid labelled "Update To:". Enter the following:

Update To: StrConv([FieldName],3)

where FieldName is the name of your field.

As with any action query, you should make a backup of your database first,
just in case you don't get the expected result. It's always easier to recover
from a backup than try to undo the results of an action (update, append,
delete) query.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have names/addresses that are either all upper, all lower or mixed,
especially names like van den STEEN. I need to be able to convert to the
proper upper/lower format.

I have tried the input mast >L<?????, but if there are more than 1 piece to
a name, it only converts the first piece (Van), leaving den STEEN as it. I
did at one point a month ago figured out how in an expression with in a
query, but for the life of me I can't find it (I will blame it on the
holidays) and I have not been able to figure it out again.

Any help is very much appreciated!

Thanks
Vivian
 
Tom,

I did find the strconv([fieldname],3) and tried in the query to avoid update
queries (especially since I am not real familar with those, but can learn).
It worked fine on most names expect those with a hyphen Ball-Baker. Any
suggestions with that?

Thanks
 
My only suggestions are:

1.) Manual correction.
2.) Write a custom function to handle data with special cases, such as a
hyphen.
3.) Purchase Splitter for Microsoft Access. This is a wonderful tool.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Vivian Baker said:
Tom,

I did find the strconv([fieldname],3) and tried in the query to avoid update
queries (especially since I am not real familar with those, but can learn).
It worked fine on most names expect those with a hyphen Ball-Baker. Any
suggestions with that?

Thanks



Tom Wickerath said:
Hi Vivian,

You can use an update query to update the values to proper case. Create a
normal select query. Apply any appropriate criteria to filter records. Then,
in query design view, click on Query > Update Query. You should see a new row
in the QBE grid labelled "Update To:". Enter the following:

Update To: StrConv([FieldName],3)

where FieldName is the name of your field.

As with any action query, you should make a backup of your database first,
just in case you don't get the expected result. It's always easier to recover
from a backup than try to undo the results of an action (update, append,
delete) query.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have names/addresses that are either all upper, all lower or mixed,
especially names like van den STEEN. I need to be able to convert to the
proper upper/lower format.

I have tried the input mast >L<?????, but if there are more than 1 piece to
a name, it only converts the first piece (Van), leaving den STEEN as it. I
did at one point a month ago figured out how in an expression with in a
query, but for the life of me I can't find it (I will blame it on the
holidays) and I have not been able to figure it out again.

Any help is very much appreciated!

Thanks
Vivian
 
Tom said:
My only suggestions are:

1.) Manual correction.
2.) Write a custom function to handle data with special cases, such as a
hyphen.
3.) Purchase Splitter for Microsoft Access. This is a wonderful tool.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Tom,

I did find the strconv([fieldname],3) and tried in the query to avoid update
queries (especially since I am not real familar with those, but can learn).
It worked fine on most names expect those with a hyphen Ball-Baker. Any
suggestions with that?

Thanks

If you go route 2.) and only need the hyphen case considered, try:

http://groups.google.com/group/microsoft.public.access/msg/a55a54e44543b129

I don't have the code I used for the general case handy.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top