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

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

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

End Function

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jeremy Sculler" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Jeremy Sculler wrote:
> 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")

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:ZkjLg.6091$(E-Mail Removed)...
> Jeremy Sculler wrote:
>> 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...!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Douglas J. Steele wrote:
> "Rick Brandt" <(E-Mail Removed)> wrote in message
> news:ZkjLg.6091$(E-Mail Removed)...
>>
>> =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 :-)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick Brandt wrote:
> >> =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.

--

"Jamie Collins" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Rick Brandt wrote:
>> >> =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>

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Douglas J. Steele wrote:
> "Jamie Collins" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > Rick Brandt wrote:
> > > > > =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. (?)


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com