Extracting the few letters after the comma

[Pietro]

Hi,
In my query i've a field called "Agent" that looks like this "John_Smith,
Peter",actually i need to make a new field in this query to display the first
name then the last name,instead of the field "agent".
I need a code that extracts the few letters after the comma,apart from its
position as it may come as the 5th letter or the 8th ....ecc.
For eample:
Field name Agent Newfield
John_Smith, Peter Peter John_Smith
Anderson ,Jack Jack Anderson
Can anybody help?

 

[Douglas J. Steele]

For the examples you've given, the text before the comma would be:

Trim(Left([Agent], InStr([Agent], ",") - 1))

and the text after the comma would be:

Trim(Mid([Agent], InStr([Agent], ",") +1))

 

[Dale Fye]

Just so you know, there is also an InstrRev( ) function, which searches for
the occurance of a particular string, starting at the right and working in
reverse. This is helpful with you know you might have more than one
occurance of the search string and know you only want info to the right of
the last one. For example:

strFullFileName = "C:\Windows\Temp\TempSubFolder\FileName.text"

To get the actual file name, without the path, you could use:

strFileName = mid(strFullFileName, instrrev(strFullFileName, "\") + 1)

In your case either InStr( ) or InStrRev( ) will return the same value.

Personally, I would recommend that you put this in a function, because if,
for some unknown reason, you have a value that doesn't contain the comma or
the comma is the last character in the string, InStr( ) will return a zero,
and will screw up your results.

Public Function fnAgentFirstName(SomeValue as Variant) as String

Dim intCurPos as integer

'Accepts a variant to handle NULL values in the field

'If a NULL was passed, return an empty string
If IsNull(SomeValue) then
fnAgentFirstName = ""
Exit Function
Endif

'Determine where the comma is, if there is one
intCurPos = instr(SomeValue, ",")

'if no comma or it is the last character in the string, return an empty
string
If intCurPos = 0 then
fnAgentFirstName = ""
Elseif intCurPos = LEN(SomeValue) Then
fnAgentFirstName = ""
Else
fnAgentFirstName = Right(SomeValue, intCurPos + 1)
endif

'Trim extra spaces from the front and back
fnAgentFirstName = TRIM(fnAgentFirstName)

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Pietro]

Thank you so much,it works fine,but unfortunately some names don't contain
comma,so i get #Error,do you suggest something ?

For the examples you've given, the text before the comma would be:

Trim(Left([Agent], InStr([Agent], ",") - 1))

and the text after the comma would be:

Trim(Mid([Agent], InStr([Agent], ",") +1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
In my query i've a field called "Agent" that looks like this "John_Smith,
Peter",actually i need to make a new field in this query to display the
first
name then the last name,instead of the field "agent".
I need a code that extracts the few letters after the comma,apart from
its
position as it may come as the 5th letter or the 8th ....ecc.
For eample:
Field name Agent Newfield
John_Smith, Peter Peter John_Smith
Anderson ,Jack Jack Anderson
Can anybody help?

 

[Douglas J. Steele]

IIf(InStr([Agent], ",") > 0, Trim(Left([Agent], InStr([Agent], ",") - 1)),
[Agent])

IIf(InStr([Agent], ",") > 0 Trim(Mid([Agent], InStr([Agent], ",") +1)),
Null)

If there's no comma, the first one will simply return the original string,
while the second one will return Null.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you so much,it works fine,but unfortunately some names don't contain
comma,so i get #Error,do you suggest something ?

For the examples you've given, the text before the comma would be:

Trim(Left([Agent], InStr([Agent], ",") - 1))

and the text after the comma would be:

Trim(Mid([Agent], InStr([Agent], ",") +1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
In my query i've a field called "Agent" that looks like this
"John_Smith,
Peter",actually i need to make a new field in this query to display the
first
name then the last name,instead of the field "agent".
I need a code that extracts the few letters after the comma,apart from
its
position as it may come as the 5th letter or the 8th ....ecc.
For eample:
Field name Agent Newfield
John_Smith, Peter Peter John_Smith
Anderson ,Jack Jack Anderson
Can anybody help?

 

[aaron.kempf]

SQL Server supports regular expressions to do this.

HTH

-Aaron