A String ?

  • Thread starter Thread starter Traveler
  • Start date Start date
T

Traveler

Hi,

Using VBA functions, how do you extract just the last name part of a string
that has a comma in it such as the following:

Brennar, Steve P.
 
Hi,

Using VBA functions, how do you extract just the last name part of a string
that has a comma in it such as the following:

Brennar, Steve P.
Click to expand...

Trim(Mid([fullname], ",") + 1))
 
Gina, John,

I have several hundred record who's name field is in the "LastName,
FirstName" format such as

Brown, Tom K.
Cassins, David J.

I'm trying to do a search on the last name part then use bookmark to bring
up the record, but the code I have keeps giving me errors. I hope I don't
have to split all those records into Firstname Lastname parts. I'm open to
suggestions on how to handle this, including (i hope not) splitting things up.

Here's the code I'm using:

Private Sub cboSearch_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim myStr As String

myStr = Me.cboSearch.Column(1)

myStr = Left$([myStr], InStr(1, [myStr], ",") - 1)

Set rst = Me.RecordsetClone
rst.FindFirst myStr
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
End Sub
 
Gina, John,

I have several hundred record who's name field is in the "LastName,
FirstName" format such as

Brown, Tom K.
Cassins, David J.

I'm trying to do a search on the last name part then use bookmark to bring
up the record, but the code I have keeps giving me errors. I hope I don't
have to split all those records into Firstname Lastname parts. I'm open to
suggestions on how to handle this, including (i hope not) splitting things up.

Here's the code I'm using:

Private Sub cboSearch_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim myStr As String

myStr = Me.cboSearch.Column(1)

myStr = Left$([myStr], InStr(1, [myStr], ",") - 1)

Set rst = Me.RecordsetClone
rst.FindFirst myStr
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
End Sub
Click to expand...

The FindFirst argument needs to be a valid SQL WHERE clause without the WHERE.
"Brown" doesn't qualify!

I'd use a simpler technique:

MyStr = "[Name] LIKE " & Me!cboSearch.Column(1) & "*"
 
John,

It appears that FindFirst does not like the comma it finds in the names. I
get the following error:

Run-time error '3077':
Syntax error (comma) in expression

I tried the earlier expression to remove the comma and try to use just the
last name part, but that didn't work either. Am i missing something?

T
John W. Vinson said:
Gina, John,

I have several hundred record who's name field is in the "LastName,
FirstName" format such as

Brown, Tom K.
Cassins, David J.

I'm trying to do a search on the last name part then use bookmark to bring
up the record, but the code I have keeps giving me errors. I hope I don't
have to split all those records into Firstname Lastname parts. I'm open to
suggestions on how to handle this, including (i hope not) splitting things up.

Here's the code I'm using:

Private Sub cboSearch_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim myStr As String

myStr = Me.cboSearch.Column(1)

myStr = Left$([myStr], InStr(1, [myStr], ",") - 1)

Set rst = Me.RecordsetClone
rst.FindFirst myStr
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
End Sub
Click to expand...

The FindFirst argument needs to be a valid SQL WHERE clause without the WHERE.
"Brown" doesn't qualify!

I'd use a simpler technique:

MyStr = "[Name] LIKE " & Me!cboSearch.Column(1) & "*"
Click to expand...
 
John,

I was reading another post named "Add values to a text field" where both you
and Allen Browne was trying to discourage the use of adding comma's to a name
field. It looks like I have a serious problem here with trying to search on
a name field with data filled with comma's. Just when I thought it couldn't
get any worse I think it just did!

Splitting the name field into two seperate fields will take forever. Geez!

T

John W. Vinson said:
Gina, John,

I have several hundred record who's name field is in the "LastName,
FirstName" format such as

Brown, Tom K.
Cassins, David J.

I'm trying to do a search on the last name part then use bookmark to bring
up the record, but the code I have keeps giving me errors. I hope I don't
have to split all those records into Firstname Lastname parts. I'm open to
suggestions on how to handle this, including (i hope not) splitting things up.

Here's the code I'm using:

Private Sub cboSearch_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim myStr As String

myStr = Me.cboSearch.Column(1)

myStr = Left$([myStr], InStr(1, [myStr], ",") - 1)

Set rst = Me.RecordsetClone
rst.FindFirst myStr
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
End Sub
Click to expand...

The FindFirst argument needs to be a valid SQL WHERE clause without the WHERE.
"Brown" doesn't qualify!

I'd use a simpler technique:

MyStr = "[Name] LIKE " & Me!cboSearch.Column(1) & "*"
Click to expand...
 
John,

It appears that FindFirst does not like the comma it finds in the names. I
get the following error:

Run-time error '3077':
Syntax error (comma) in expression

I tried the earlier expression to remove the comma and try to use just the
last name part, but that didn't work either. Am i missing something?
Click to expand...

No, I was <blush>... my apologies, need the syntactically required quote
marks:

MyStr = "[Name] LIKE """ & Me!cboSearch.Column(1) & "*"""

That's three consecutive " marks after LIKE and again at the end; two
consecutive quotemarks in a string delimited by quotemarks are converted to a
single quotemark, giving you (e.g.)

[NAME] LIKE "Brown*"

or

[NAME] LIKE "O'Brien"
 
John W. Vinson,

You are an absolute Access GOD. I had to move things from BeforeUpdate to
AfterUpdate, but once I did it works like a charm. My god man, you have
saved me tons of work and for that I am on my floor bowing to a makeshift
statue with your name written on it :-)

I cannot thank you enough. We need some clones of you so that you never
leave us.

T

John W. Vinson said:
John,

It appears that FindFirst does not like the comma it finds in the names. I
get the following error:

Run-time error '3077':
Syntax error (comma) in expression

I tried the earlier expression to remove the comma and try to use just the
last name part, but that didn't work either. Am i missing something?
Click to expand...

No, I was <blush>... my apologies, need the syntactically required quote
marks:

MyStr = "[Name] LIKE """ & Me!cboSearch.Column(1) & "*"""

That's three consecutive " marks after LIKE and again at the end; two
consecutive quotemarks in a string delimited by quotemarks are converted to a
single quotemark, giving you (e.g.)

[NAME] LIKE "Brown*"

or

[NAME] LIKE "O'Brien"
Click to expand...
 
Gina,

Thank you for your reply. That page you referred me to is magnificent. I
found a wealth of knowledge there along with answers and solutions to
questions I have had in the past and wondered how to do it. It's a website
that I will visit often and is now among my top favorites for Access stuff.

Thanks again Gina for your great post.

T
 
Back
Top