creating a string

[Mike]

I am trying to string together several elements of data
into a 5 digit string.
the first 2 characters are alpha
the next 3 characters are numeric.

the problem i am having is that the numeric portion of
the string (last 3 characters) are referenced from a
different table and are not always 3 characters.
i.e. if the referenced number is 2, i need to have this
stringed together as:

XX002

right now it is being stringed together as (XX2)

i am using the following string command:
"xx" & [table reference][field ref]

can anyone help me with how to make sure that this string
length is 5 characters?

any help is appreciated.
Thanks,

 

[Newbie]

Try
right$("000" & fieldref, 3)

HTH

 

[Peter R. Fletcher]

That's rather cute! The textbook way to do it would probably be to use
Format() on the numeric, but your way may be faster, too.

Try
right$("000" & fieldref, 3)

HTH

I am trying to string together several elements of data
into a 5 digit string.
the first 2 characters are alpha
the next 3 characters are numeric.

the problem i am having is that the numeric portion of
the string (last 3 characters) are referenced from a
different table and are not always 3 characters.
i.e. if the referenced number is 2, i need to have this
stringed together as:

XX002

right now it is being stringed together as (XX2)

i am using the following string command:
"xx" & [table reference][field ref]

can anyone help me with how to make sure that this string
length is 5 characters?

any help is appreciated.
Thanks,

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Peter De Baets]

Use the "xg_lPad" function below to left-pad your number with "0"'s:

Function xg_Repeat(sStringToRepeat As String, iNumOfTimes As Integer)
As String
Dim i As Integer
Dim s As String
s = ""
For i = 1 To iNumOfTimes
s = s & sStringToRepeat
Next i
xg_Repeat = s
End Function

Function xg_lPad(sStringToPad As String, sPadChar As String,
iTotalDesiredLengthOfString As Integer) As String
'* Pads characters on the left of a string out to a desired total
string length
'* Returns the padded string
xg_lPad = xg_Repeat(sPadChar, iTotalDesiredLengthOfString -
Len(Trim(sStringToPad))) & Trim(sStringToPad)
End Function


Ex.: MsgBox xg_lPad("2", "0", 3) gives "002"

Hope this helps,

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com