make text field to certain length and format

[RichardM via AccessMonster.com]

I would like to transform (in query) a text field into formated text field
(starting with X and having allways 16 letter places). For example word
"cat"would become
X cat
and word "citizen" would be
X citizen
Any idea how this could be done? I assume that a function is needed but I'm
not the best in VB.

 

[RonaldoOneNil]

Create a column in your query with this

Padded: "X" & Right(String(15,32) & [YourTextField],15)

 

[DaveT]

You can use fixed length string sunch as:

<>
Public Function SetTheString(yLeft, yRight) As String

Dim strText As String * 16 'fixed length string
Dim strLeftStuff As String
Dim strRightStuff As String

On Error Resume Next

SetTheString = ""
strText = ""

strLeftStuff = Nz(yLeft)
strRightStuff = Nz(yRight)

RSet strText = strRightStuff
Mid(strText, 1) = strLeftStuff

SetTheString = strText

End Function
<>
Debug window example:

? SetTheString("x", "ciitizen")
x ciitizen