Remove Letter from Field

[Clownfish1980m]

This is an unusual problem. My wife downloaded a file from the Virginia State Immunization Registry and wants to upload the file into their electronic medical records. It downloads the file into Excel except it added an “A” or “N” in front of the first name. We tried to use the “find and replace” but the file is so large that it would take forever to get done. We tried the find and replace the “A” with “” but it removes all the “A”s including those in the name. I figured that I could import the file into access and then come up with a VBA code to just remove the “A” or “N” from the first name either in the current field (FName) or create a second field (FName2). I could then build a query and export to excel. The first name would be correct and ready to upload. Does anyone have any suggested VBA code I could use to accomplish this? Thanks

 

[John Big Booty]

Use;

[URL="http://www.techonthenet.com/access/functions/string/replace.php"]Replace[/URL](Me.YourFieldName, "A", "", 1, 1)

To remove a leading A and

Replace(Me.YourFieldName, "N", "", 1, 1)

To replace a leading N

 

[Clownfish1980m]

What event procedure would I put that under? Can I put both codes in so that I does the whole file at one time? Thanks Ken

 

[John Big Booty]

You could create a couple of Update Queries one with the Update To: criteria set to;

Replace([YourFieldName],"N","",1,1)

the other to;

Replace([YourFieldName],"A","",1,1)

I would then import your data into a Temporary holding table where you can safely run your two update queries on your imported data before transferring it to it's final destination table.

 

[Galaxiom]

Use;

[URL="http://www.techonthenet.com/access/functions/string/replace.php"]Replace[/URL](Me.YourFieldName, "A", "", 1, 1)

That is not going to work. It will remove the first instance of the letter "A" regardless of whether it is the first letter in the string or not.

From you description you simply need to remove the first letter of the value for every record.

Use this expression in the Update

Mid(fieldname,2)

 

[John Big Booty]

Ah yes Correct.