Trim off extra characters at the end of each line

[cwhite23]

Does anyone out there have an answer for this one?

We are currently in the process of changing out our existing accounting package for a new one. When we started importing the data into the new system the decision was made that for each vendor the vendor's old ID number would be tacked on to the end of their name. We did this, making sure to seperate the Vendor's name and ID by two blank spaces. For example, Company "ABC" had a code of "123" in the old system. The new Company name would be "ABC 123". Now management has decided that they would rather just do away with the old numbering system entirely and want's the numbers removed. Does anyone know how I can tell VBA how to search through each vendor name line and remove the bad data? I have already set up a DAO connection in VB, but don't know how to do a search on a line-by-line basis. I would rather not have to re-import the data, as people have already started entering new data into the system. Any help would be deeply appreciated.

 

[earthandfire]

The following should do what you need

[Field]= Left([Field],Instr([Field]," ") - 1)

Hope this helps.

 

[PHV]

Why not simply an Update query ?
UPDATE yourTable
SET yourField = Trim(Left([yourField],InStrRev([yourField],' ')))
WHERE yourField Like '* [0-9]*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cwhite23]

Thanks to both of you. I was trying to do this through VBA, which turned out to be the hard way!