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.

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

"Ann" wrote:

> 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.
>

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!


"Linq Adams via AccessMonster.com" wrote:

> 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.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200811/1
>
>

"Apprentice" <(E-Mail Removed)> wrote in message
news:2A39CECF-BFBA-4FAD-9749-(E-Mail Removed)...
> 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!
>
>
> "Linq Adams via AccessMonster.com" wrote:
>
>> 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.
>>
>> --
>> There's ALWAYS more than one way to skin a cat!
>>
>> Answers/posts based on Access 2000/2003
>>
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/For...dules/200811/1

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.

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!


"Stuart McCall" wrote:

> "Apprentice" <(E-Mail Removed)> wrote in message
> news:2A39CECF-BFBA-4FAD-9749-(E-Mail Removed)...
> > 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!
> >
> >
> > "Linq Adams via AccessMonster.com" wrote:
> >
> >> 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.
> >>
> >> --
> >> There's ALWAYS more than one way to skin a cat!
> >>
> >> Answers/posts based on Access 2000/2003
> >>
> >> Message posted via AccessMonster.com
> >> http://www.accessmonster.com/Uwe/For...dules/200811/1
>
> 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.
>
>
>