Display certain characters

[Ann]

I want to display only certain characters from a field. I'm not a programmer
so I'm unsure how do to this. It's a Customer Description text field and the
data is listed as last name, underscore, first name, e.g., Doe_John.

I want to create a new field with only the customers initials so I need to
pull the first letter of the first name and the first letter of the last name
so I end up with JD. Can anyone help me out? Thanks in advance.

 

[BeWyched]

Hi Ann

Use the Split and Left functions. e.g.:

Dim Arr, strName, strInitials
strName = "Doe_John"
Arr = Split(strName, "_")
strInitials = Left(Arr(1), 1) & Left(Arr(0), 1)

Cheers.

BW

 

[Linq Adams via AccessMonster.com]

You don't say where you want to do this. Assuming it's in a form, this will
do it, where

YourNameField

is the textbox holding Doe_John

and

YourInitialsField

is the textbox to hold JD

Private Sub YourNameField_AfterUpdate()
Me.YourInitialsField = Mid(Me.YourNameField, InStr(Me.YourNameField, "_") +
1, 1) & Left(Me.YourNameField, 1)
End Sub

Since this is a calculated field you wouldn't want to store it in a table,
simply re-calculate it when needed. To do this in a query, you'd use

YourInitialsField: Mid([YourNameField], InStr([YourNameField], "_") + 1, 1) &
Left([YourNameField], 1)

and then simply refer to the calculated field

YourInitialsField

whenever you needed it.

 

[Apprentice]

This almost works. I am using it in a form for an Initials field, but the
result is showing "Two Letters" both of the first name. Here is the code as
I have it.

Private Sub ActivityLead_AfterUpdate()
Me.LeadInt = Mid(Me.ActivityLead, InStr(Me.ActivityLead, "_") + 1, 1) &
Left(Me.ActivityLead, 1)
End Sub

 

[Stuart McCall]

This almost works. I am using it in a form for an Initials field, but the
result is showing "Two Letters" both of the first name. Here is the code
as
I have it.

Private Sub ActivityLead_AfterUpdate()
Me.LeadInt = Mid(Me.ActivityLead, InStr(Me.ActivityLead, "_") + 1, 1) &
Left(Me.ActivityLead, 1)
End Sub
--
Your guidance is greatly appreciated!

You don't say where you want to do this. Assuming it's in a form, this
will
do it, where

YourNameField

is the textbox holding Doe_John

and

YourInitialsField

is the textbox to hold JD

Private Sub YourNameField_AfterUpdate()
Me.YourInitialsField = Mid(Me.YourNameField, InStr(Me.YourNameField,
"_") +
1, 1) & Left(Me.YourNameField, 1)
End Sub

Since this is a calculated field you wouldn't want to store it in a
table,
simply re-calculate it when needed. To do this in a query, you'd use

YourInitialsField: Mid([YourNameField], InStr([YourNameField], "_") + 1,
1) &
Left([YourNameField], 1)

and then simply refer to the calculated field

YourInitialsField

whenever you needed it.

Threason it's resulting in 2 characters is because that's what you're
selecting. The expression:

Mid(Me.ActivityLead, InStr(Me.ActivityLead, "_") + 1, 1)

returns the 1st character following the 1st underscore. Then the expression:

Left(Me.ActivityLead, 1)

returns the 1st character in the string.

Delete whichever expression you don't need.

 

[Apprentice]

I got it... thanks. I just needed to swap the two lines to get 1st + 2nd
int, instead of 2nd + 1st.

If anyone needs it, here is the updated code:

Private Sub ActivityLead_AfterUpdate()
Me.LeadInt = Left(Me.ActivityLead, 1) & Mid(Me.ActivityLead,
InStr(Me.ActivityLead, " ") + 1, 1)

End Sub

Thanks again
--
Your guidance is greatly appreciated!

This almost works. I am using it in a form for an Initials field, but the
result is showing "Two Letters" both of the first name. Here is the code
as
I have it.

Private Sub ActivityLead_AfterUpdate()
Me.LeadInt = Mid(Me.ActivityLead, InStr(Me.ActivityLead, "_") + 1, 1) &
Left(Me.ActivityLead, 1)
End Sub
--
Your guidance is greatly appreciated!

You don't say where you want to do this. Assuming it's in a form, this
will
do it, where

YourNameField

is the textbox holding Doe_John

and

YourInitialsField

is the textbox to hold JD

Private Sub YourNameField_AfterUpdate()
Me.YourInitialsField = Mid(Me.YourNameField, InStr(Me.YourNameField,
"_") +
1, 1) & Left(Me.YourNameField, 1)
End Sub

Since this is a calculated field you wouldn't want to store it in a
table,
simply re-calculate it when needed. To do this in a query, you'd use

YourInitialsField: Mid([YourNameField], InStr([YourNameField], "_") + 1,
1) &
Left([YourNameField], 1)

and then simply refer to the calculated field

YourInitialsField

whenever you needed it.

Threason it's resulting in 2 characters is because that's what you're
selecting. The expression:

Mid(Me.ActivityLead, InStr(Me.ActivityLead, "_") + 1, 1)

returns the 1st character following the 1st underscore. Then the expression:

Left(Me.ActivityLead, 1)

returns the 1st character in the string.

Delete whichever expression you don't need.