Add text to a number (concatenate?)

[neilwebber]

Here's a quick one for the experts out there.

I have a field (PCLNumber) that contains a number in each record.
I want to change this field so that it fits the format

PCL00000

ie I want to add 'PCL' to each record and the right amount of zeros so that each numerical part is five figures long.

eg The entry 123 in Field PCLNumber would become PCL00123
The entry 12 in Field PCLNumber would become PCL00012 etc

I know this should be easy but I just can't find a specific reference on how to do this and my coding isn't up to much.

thanks very much
Neil

 

[GJT]

Try this:

The String() function returns a string populated with the appropriate number (first param) of characters (char- second param) ......

Dim str As String, nVal As Integer, strVal As String

nVal = 23
strVal = nVal
str = "PCL"
str = str & String(5 - Len(strVal), "0") & strVal
MsgBox str
End Sub

 

[RV]

If you want to update all your PCLnumbers, you can run an update query (based on GJT's code):

UPDATE <yourtable>
SET PCLNumber = "PCL" &String(5 - Len(PCLNumber), "0") & PCLNumber;

RV

 

[Pat Hartman]

neilwebber,

"PCL" & Format(PCLNumber,"00000")

RV,
Since PCLNumber is numeric, you can't turn it into a string.

 

[neilwebber]

Thanks all for your suggestions - much appreciated.

I tried Pat's first as it seemed the most simple and it worked a treat straight away - thanks Pat. Now that I see how this sort of query is constructed I can take it away and fit it to one or to other similar things I need to do.

Thanks again for your help

Neil