Convert integer to formatted text string..

[Jeremy Sculler]

If I have a column with integers:

12345
2345
345

... and want to leftpad the first digits with zeros and insert a space in
front of the two last digits like this:

12345 -> 0123 45
2345 -> 0023 45
345 -> 0003 45

how can that be done in access..?

regards

Jeremy

 

[Douglas J. Steele]

Function PadNumber(NumberIn As Long) As String
Dim strNumber As String

strNumber = Format(NumberIn, "000000")
PadNumber = Left(strNumber, 4) & " " & _
Mid(strNumber, 5)

End Function

 

[Rick Brandt]

If I have a column with integers:

12345
2345
345

.. and want to leftpad the first digits with zeros and insert a space
in front of the two last digits like this:

12345 -> 0123 45
2345 -> 0023 45
345 -> 0003 45

how can that be done in access..?

=Format(Format([FieldName], "000000"), "0000 00")

 

[Douglas J. Steele]

If I have a column with integers:

12345
2345
345

.. and want to leftpad the first digits with zeros and insert a space
in front of the two last digits like this:

12345 -> 0123 45
2345 -> 0023 45
345 -> 0003 45

how can that be done in access..?

=Format(Format([FieldName], "000000"), "0000 00")

I knew there had to be a better way...!

 

[Rick Brandt]

=Format(Format([FieldName], "000000"), "0000 00")

I knew there had to be a better way...!

Actually, I was fully expecting someone to come up with a better way than
mine

 

[Jamie Collins]

=Format(Format([FieldName], "000000"), "0000 00")

I knew there had to be a better way...!

Actually, I was fully expecting someone to come up with a better way than
mine

How about

Format([FieldName], "0000 00")

Jamie.

--

 

[Douglas J. Steele]

=Format(Format([FieldName], "000000"), "0000 00")

I knew there had to be a better way...!

Actually, I was fully expecting someone to come up with a better way than
mine

How about

Format([FieldName], "0000 00")

D'oh! <g>

 

[Rick Brandt]

=Format(Format([FieldName], "000000"), "0000 00")

I knew there had to be a better way...!

Actually, I was fully expecting someone to come up with a better
way than mine

How about

Format([FieldName], "0000 00")

D'oh! <g>

That is what I tested first and I could have sworn that there was somethign
about it that didn't work correctly on the shorter numbers. However, in trying
it now I can't see any problems. (?)