Trim *

[micang]

Access 2003

Hi All,


How would I go about triming a text field which has * as the leading and trailing character?

E.G. - I have *ERT46676864R* and I want to remove the two *'s, therefore be left with ERT46676864R

I can't figure out how to get access to identify the * character.

Any help much appreciated.

Michael

 

[AlexCuse]

I think this is what you want:

Replace(Column, '*', '')

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[micang]

Thanks Alex, that should get me to my result. I was not aware of the REPLACE function.

Ta

Michael

 

[AlexCuse]

Glad it worked (or at least you think it will work)

You are only worried about *'s right (no other special characters), and there is no chance of a * ending up in the data you want to keep?

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

I doubt it, looks like what's read from a bar code scanner. Most include the * at the beginning and end as terminating characters.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[micang]

Hi Alex,

It has worked. I did have other characters that needed to be removed, like '/' , '.' , '-' which are at either at the beginning or end of string.

Therefore I applied the replace for each character.

Basically what I have is data strings that are alphanumeric, any characters other than a number or letter in the begining or end of string, need to be removed.

I am not sure if there is an easier way of doing this other than the replace function.

The way I have done it is as below:

UPDATE HGV SET HGV.Amended_VIN = Replace([amended_vin],'*','')
WHERE (((Left([HGV]![Amended_VIN],1))='*'));

I have run this for each character I found by doing a group by on left 1, right 1 etc etc.

Thanks for the help.

Michael

 

[lespaul]

guess it's not a bar code after all, glad Alex was able to help though!

leslie

 

[micang]

yep, not a bar code! A vehicle indentification number - close enough

Thanks.

Michael

 

[HarleyQuinn]

Do all of the VIN's have a leading and trailing character you need(ed) removing?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[micang]

Hi Harley,

Nope, not all. Some also have more than 1 leading/trailing character to be removed.

examples

*BNG1245879664. - should be BNG1245879664
**HGF87POUGH56 - should be HGF87POUGH56
/UYR651DFWG198** - should be UYR651DFWG198

I have circa 45 million VIN's, I would estimate that around +- 150,000 have characters that need to be removed.

I did a batch of around 700,000 with the method above and found 13,000 with characters needing removing.

Many thanks.

Michael

 

[TomCologne]

Consider using a function like the one here:

ACC2000: How to Strip Specific Characters from a String

http://support.microsoft.com/default.aspx?scid=kb;en-us;210227

TomCologne

 

[micang]

Many thanks TomCologne.