upper case / lower case

  • Thread starter Thread starter iain
  • Start date Start date
I

iain

Hi,

Can anyone let me know if there is a function within
Access to change upper case to lower case automatically?

Cheers,
Iain
 
also, i've sussed how to force upper or lower case when
fields are populated, using...

Private Sub Publisher_AfterUpdate()
Me!Publisher = LCase(Me!Publisher)
End Sub

on After Update.

but what should LCase or UCase be replaced with to force
Proper Case?

Cheers,
Iain
 
For proper case try:
Me!Publisher = StrConv(Me.Publisher, vbProperCase)

Victor Delgadillo
 
The function you want for conversion to proper case is:
StrConv(Me!Publisher, 3)

The argument, 3, signifies conversion to proper case
 
thanks guys - thats the badger.

is there a way of applying this to existing records
universally?

cheers,
Iain
 
Iain,

You could do an Update Query. If you are updating field Publisher, put the
following in the Update To row of the Update query:

StrConv(Publisher, 3)

Note that what this function does is capitalize the first character after a
space, so names like MACDONALD would be converted to Macdonald. You might
have to do a little manual cleanup.
 
Iaia,
You would need to write an update query.

Update YourTable Set YourTable.FieldName = strConv([FieldName],3) Where
FieldName is not null;

Note: while you can use strConv([FieldName],vbProperCase) in VBA code, in an
Access query, or a control source on a form or a report, you must use the
vbProperCase value, which is 3.

Be aware also that this will sometimes give un-expected results with names.
McDonald will become Mcdonald, O'Connor becomes O'connor, van der Meer - Van
Der Meer, CBS - Cbs, NASA - Nasa, etc.

You can use an exceptions table and DLookUp first before converting.

Then again, some people with the same names do capitalize their names
differently, so there is no guarrenty strConv will correctly capitalize the
name.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
Remove nothing from my address
 
but what should LCase or UCase be replaced with to force
Proper Case?
Click to expand...

You will need to write a function that you can call from a query. Here is
the code for the one I use.

Function ChangeToProper(P_String) As String
ChangeToProper = StrConv(P_String, vbProperCase)
End Function

In a query I call it like this

UpperCase: ChangeToProper([FieldName])

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
 
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain
 
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
 
Fred,

Thanks for that - that code was above and beyond the call
of duty, but much appreciated!

I've never really got as technical as using Modules before
(except as I said before by looking behind the scenes of
field properties and copy / paste, or simple if..then..
for some conditional formatting and the like).

So i'm a bit unsure about how to call the module from a
query as you described at the end.

At the moment I've got three update queries for lower,
upper, and proper case, each just using Update to : StrConv
([Staff],2) etc, and buttons on a form to run them.

Should I be using a different query for the Proper Case
instance that calls the module, or should it be
incorporated into the existing query?

I've also tried putting

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If

as the After Update to the field I'm converting in the
form, but its not doing anything.

Hopefully by now its nearly there, so won't have to hassle
you much more!

Thanks again,
Iain
-----Original Message-----
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr (34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup
Click to expand...
("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain
field
Publisher, put the first
character after a
Click to expand...


.
Click to expand...
 
Hi,

I've created a dummy database to play around with this.

I basically have two tables :

Table1 - with StaffID & Staff fields
tblExceptions - ID & ExceptName fields

One Form :

Form1, based on Table1 with StaffID and Staff fields
and 3 buttons running update queries

Lower Case, Upper Case and Proper Case using Update to :
StrConv([Staff],2) etc

And one module - Module1 which is the code you posted.

So I'm stuck at where to call this Module into play..

You mentioned calling it from a query - but what should
this query be based on and contain?

Sorry if I'm missing the obvious...

Iain

-----Original Message-----
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr (34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup
Click to expand...
("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain
field
Publisher, put the first
character after a
Click to expand...


.
Click to expand...
 
ok - got as far as putting in

If Not IsNull([Staff]) Then
[Staff] = ConvExceptions([Staff])
End If

and this is working insomuch as it automatically converts
new entries in Table1 to proper case - but it isn't
flagging up or changing anything entered as an exception
in tblExceptions.

Iain
-----Original Message-----
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr (34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup
Click to expand...
("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain
field
Publisher, put the first
character after a
Click to expand...


.
Click to expand...
 
....

and I've got as far as an Update Query, based on Table1
where :

Field = Staff
Table = Table1
Update to = ConvExceptions([Staff])
Criteria = Is Not Null

which is probably what you said originally.

Again tho' it works insomuch as it changes everything to
proper text as Update to : StrConv([Staff],3) would, but
its not flagging the exceptions....

Any ideas? It must almost be there, so would be great to
nail it...

Cheers,
Iain

-----Original Message-----
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr (34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup
Click to expand...
("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain
field
Publisher, put the first
character after a
Click to expand...


.
Click to expand...
 
Iain,
Take a look at the Update query SQL expression.
You can find it by clicking on the SQL View tool button located on the query
design tool bar.

It should look like this:

UPDATE Table1 SET Table1.Staff = ConvExceptions([Staff])
WHERE (((Table1.Staff) Is Not Null));

Then go back to the module in which you place the
ConvExceptions function.

Double check everything against my original post.
Make sure you haven't misspelled a word or omitted any punctuation.
make sure the If's and the End If's match.

It does work. I just tried it again in an Update Query to test it.

Also in two previous posts, you mention using strConv([FieldName]2).
That is equivalent to Lower Case, not Proper Case. You must use
strConv([FieldName],3) to Capitalize The First Letters.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
...

and I've got as far as an Update Query, based on Table1
where :

Field = Staff
Table = Table1
Update to = ConvExceptions([Staff])
Criteria = Is Not Null

which is probably what you said originally.

Again tho' it works insomuch as it changes everything to
proper text as Update to : StrConv([Staff],3) would, but
its not flagging the exceptions....

Any ideas? It must almost be there, so would be great to
nail it...

Cheers,
Iain

-----Original Message-----
Iain,

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Paste this function into a new module.

Public Function ConvExceptions(strIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr (34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup
Click to expand...
("[ExceptName]", "tblExceptions", "[ExceptName] = " &
Chr(34) & strIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(strIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

Hope this has helped.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


iain said:
thanks again guys.
thats working great now.

can you point me in the right direction for how to
incorporate the Lookup table for exceptions?

Iain

-----Original Message-----
Iain,

You could do an Update Query. If you are updating field
Publisher, put the
following in the Update To row of the Update query:

StrConv(Publisher, 3)

Note that what this function does is capitalize the first
character after a
space, so names like MACDONALD would be converted to
Macdonald. You might
have to do a little manual cleanup.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

thanks guys - thats the badger.

is there a way of applying this to existing records
universally?

cheers,
Iain

-----Original Message-----
The function you want for conversion to proper case is:
StrConv(Me!Publisher, 3)

The argument, 3, signifies conversion to proper case

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

message
also, i've sussed how to force upper or lower case
when
fields are populated, using...

Private Sub Publisher_AfterUpdate()
Me!Publisher = LCase(Me!Publisher)
End Sub

on After Update.

but what should LCase or UCase be replaced with to
force
Proper Case?

Cheers,
Iain


-----Original Message-----
Hi,

Can anyone let me know if there is a function within
Access to change upper case to lower case
automatically?

Cheers,
Iain
.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Fred,

Think I've sussed where I was going wrong - I had just
the surname as an exception, rather the full name, which
of course is what the string would be.

Iain
 
very last question. honest!

is there a way of getting it to recognise things like Mac
or Mc or any exceptions like that generally, using
Like "Mac*" or similar somewhere in there?

if not, thank you very much for all that helkp with that
one - it was very much appreciated.

Iain
 
Iain,

Here are 2 ways...
You could add to the function, right after the strFind = DLookUp(etc.) line:
(Let's not forget the various capitalizations of names strarting with O'.)

1) If Instr(1,strFind,"Mac") or InStr(1,strFind,"Mc") or
InStr(1,strFind,"O'") Then
MsgBox "Found a " & strFind
End If

Or you can use:

2) If strFind Like "Mac*" or strFind Like "Mc*" or strFind Like "O'" Then
etc.

"Mac" or "Mc" must be the first characters in the field.
 
Back
Top