Using VBA, How Can I Replace A Character In A Field?

[NoviceUser2008]

Hello and first of all thank you for looking at this post and helping me if
what I ask is possible. I am a novice user trying to learn Access and VBA
and my database works great except for this bug and this is my last stumbling
block I would like to overcome!

What I have is a Access database membership program for which a drivers
license reader pulls data from the license when swiped and stores that
apprpriate data in appropriate fields. For example the First name is placed
in table into a field called FirstName, Last Name into a field called
LastName etc.

My problem is the reader is not parsing out a separation character in the
parse found within the last name. For example, when seiping a John Doe Jr
license the first name John is parsed out and placed in the FirstName field
but when parsing out the Doe Jr, Doe$Jr is placed in the LastName field.

I need to be able to remove the $ and replace it with a space so the
LastName field will fill in a Doe Jr. I feel this is possible using VBA
through beforeupdate? I cannot find a solution for the code. Any help would
be greatly appreciated. Thank you once more.

Oh, almost forgot ....... the way the reader works is it pulls data by what
it is programed to pull matching the tab order in the Access form and inserts
<CR>'s to move throughout the Access form. The space for $ swap must take
place before the reader enters a <CR> and moves to the next field.

Janice

 

[Marshall Barton]

NoviceUser2008 wrote:
....

My problem is the reader is not parsing out a separation character in the
parse found within the last name. For example, when seiping a John Doe Jr
license the first name John is parsed out and placed in the FirstName field
but when parsing out the Doe Jr, Doe$Jr is placed in the LastName field.

I need to be able to remove the $ and replace it with a space so the
LastName field will fill in a Doe Jr.

The Replace function can do that (see VBA Help for details)

E.g. LastName - Replace(LastName, "$", " ")

 

[NoviceUser2008]

I need to be able to remove the $ and replace it with a space so the LastName
field will fill in a Doe Jr.

REPLACE(strFixMe, strFindString, strReplaceString)

Sub LastNameField_BeforeInsert()
Me.LastNameField=REPLACE(Me.LastNameField, "$", " ")
End Sub

 

[NoviceUser2008]

Thank you Pieter. This is the exactly code I needed but I instead used it
in the AfterUpdate property and it works perfectly to be able to work
inconjucntion with the reader and it works perfectly!

Thank you so much for your help!

Janice

 

[NoviceUser2008]

Hello Marshall,

Thank you for taking the time to help me and post your solution. I read
about the Replace but was not able to go that route. I have managed to get
it working for me however.

Thanks again and have a good day!

Janice