copying number to text field and attach zeroes (Access 2k)

Topic

New Reply

does anyone know how I can add zeroes in front of a number? for eg i have a number field which I want to stoare as a text field (different field) but before storing it as a text field I want to attach 000 in front of that number so that the number 5 will be stored 0005.

Reply | Quote

Replies

You can create a calculated column in a query. If you want to prefix ALL values with 3 zeros:

FieldWith3Zeros: “000” & [FieldName]

or if you want to use leading zeros so that all values are length 4:

FieldWithLength4: Format([FieldName],”0000″)

Reply | Quote

Thank you. Did not realize it was so simple!

Reply | Quote

You can create a calculated column in a query. If you want to prefix ALL values with 3 zeros:

FieldWith3Zeros: “000” & [FieldName]

or if you want to use leading zeros so that all values are length 4:

FieldWithLength4: Format([FieldName],”0000″)

Reply | Quote

try something like this:

Dim lenOfField as long

lenOfField = len(yourField)
Select Case lenOfField
Case 1
yourTextField = “000” & yourField

Case 2
yourTextField = “00” & yourField

Case 3
yourTextField = “0” & yourField

Case else
yourTextField = yourField

End Select

Reply | Quote

try something like this:

Dim lenOfField as long

lenOfField = len(yourField)
Select Case lenOfField
Case 1
yourTextField = “000” & yourField

Case 2
yourTextField = “00” & yourField

Case 3
yourTextField = “0” & yourField

Case else
yourTextField = yourField

End Select

Reply | Quote