Strip Some Characters from Numbers (1 Viewer)

Moore71 · May 19, 2017

Hi,
I have some numbers like this: (EE:39:F:BA:63:BF), how can I strip-off the colon from off this numbers, leaving it like this (EE39DFBA63BF) programmatically? This number is stored in table and I want to stripe the colon off, and let it be just an ordinary text
Thank you

June7 · May 19, 2017

Replace([fieldname],";","")

That expression can be used in query or in textbox.

Uncle Gizmo · May 19, 2017

Put this function:-

Code:

Function fReplaceColon(strTextIn As String) as String

    fReplaceColon = Replace(strTextIn, ":", "")

End Function      'fReplaceColon

in a stand-alone module.

Call it in the query placing the field name you want to remove the colons from in it.

So in the query designer grid, put this function by itself in a new column, something like......

Code:

fReplaceColon(fldYourFieldName)

Moore71 · May 20, 2017

Thanks for your quick response. I sure hope this gonna work, without doubt.

Thank you

June7 · May 21, 2017

Uncle Gizmo, why do you recommend a custom function? Why another layer of processing?

Uncle Gizmo · May 21, 2017

June7 said:
Uncle Gizmo, why do you recommend a custom function? Why another layer of processing?

That's a good question. There's usually at least three ways of doing something in MS Access. The third way in this case would be, to take the string and walk through it, examining each character and replacing them as required. This would be an even more complicated function, however it would provide more flexibility.

One of my pet hates is massive IIf statements contained within queries. This is how they start, with a simple statement like that, then something else gets added, then something else, then ...... This results in a difficult to read and practically unmanageable mess.

By putting your code in a separate function you have the opportunity to comment it, improve it, reuse it.

Strip Some Characters from Numbers (1 Viewer)

Moore71

DEVELOPER

June7

AWF VIP

Uncle Gizmo

Nifty Access Guy