Initial Caps Problem

[Wendymel]

I have a DB table with a text field for names. The data was copied from an
excel spreadsheet and is in the format of first initial, last name (JSMITH).
It is currently in all caps. I want all names in this field to be formatted
to display as (JSmith). I have tried to use "upper" and other formatting
options to the field in the table but with no luck.

Please Help!
Wendy

 

[Al Campagna]

Wendymel,
Upper is an Excel function. In Access it's UCase and LCase.

= Left(ExcelData,2) & LCase(Mid(Exceldata,3))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Wendymel]

This did not work. The field name is ProjectManager so this is what I used

= Left(ProjectManager,2) & LCase(Mid(ProjectManager,3))

Instead of my Project Manager name looking like this (JSMITH) and left
justified, it is now right justified and I can enter it as JSMITH or Jsmith,
not JSmith like I want.

Thanks,
Wendy
"

 

[Al Campagna]

Wendymel,
Since you never provided the name of the field, I had to use an
"example" name.
Whenever a responder suggests code, you need to convert it to your
situation.

There is a difference between correcting data that has already been
entered, and formatting data upon entry.
You wrote...
You have two issues.
One is how to change the Excel data in your table to the format you
want.
Two is how to handle the formatting of your own manual data entry.

You'll need to do an update query against the excel data in your table.
= Left(ProjectManager,2) & LCase(Mid(ProjectManager,3))
will physically change the existing excel data to the format you want.

As far as your data entry into the ProjectManager field...
Use the AfterUpdate event of ProjectManager to do the following...
Me.ProjectManager = Left(ProjectManager,2) &
LCase(Mid(ProjectManager,3))
No matter how you enter the name, this code will put it in the format
you want.

**Realize that not all names will convert properly...ex. names like
BMacarthur, or JVandorn, etc...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Wendymel]

Thanks, the update query worked to update the tables. However, I can not
make the data entry part work for this. I will try to explain with enough
detail.

I put in an after update event for the Project Manager field on the data
entry form. The data entry form is called "frmProjectEntry" The field itself
is a combo box and is named "cboProjectManager". The control source for this
field is from a table called tblProjectData thus it is
"tblProjectData.ProjectManager".

The vb for the event looks like this:

Private Sub cboProjectManager_AfterUpdate()
Me.cboProjectManager = Left(ProjectManager, 2) & LCase(Mid(ProjectManager, 3))
End Sub

I have tried other variations but it still does not work.

Thanks for your help so far.

Wendy

 

[Al Campagna]

Wendymel,
The tblProjectData should be the RecordSource for the form.. either as a
table, or as a query.

The ControlSource for cboProject Manager should be ProjectManager (not
tblProjectData.ProjectManager)
In design mode, place your cursor in the the cboProjectManager Control
Source property text box. Use the arrow on the right to select
ProjectManager from the list. If you've done everything right... it should
be there.

The code looks OK to me. It should take jsmith or JSMITH or JsMitH, and
return JSmith.
(Put a Beep into the AfterUpdate code, so you know it's "firing")

If you still have problems, would you provide a bit more detail as to
how it "doesn't work?"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Wendymel]

The reason I have the control source for the combo box as
"tblProjectData.ProjectManager" is because the record source for the form is
a query with several joined tables. The main table IS tblProjectData, but
there are numerous other tables, one of which is tblProjManager with a field
named ProjectManager. (I know...should have named it something different but
was to far along once I realized....).

Hence, the control source on the combo box with the specific name, to
differentiate the two "ProjectManager" fields.

So...having said all that, I have added a DoCmd.Beep to the original code
and it does not beep. I get the message that the name I add "is not
currently in the list. Do you want to add it?" which is what it is supposed
to do (On Not In List event). I then get a run-Time error '424': Object
Required. When I click Debug it points to the line of code we are working
with.

When I end the debugger I am back at the form with the field populated with
the name I just tried to add, but in the format "jsmith".

Thanks,
Wendy