Concatenate missing zeroes

[hobbit2612 via AccessMonster.com]

Hi,

I wonder if someone can help me please.

I am using the following formula to concatenate two fields called
'CostCentre" and "ReferenceNumber"

[CostCentre] & "-" & [ReferenceNumber]

On their own, the cost centre number looks like E01, E02 etc which is
formatted as a text field, and the Reference Number is shown as 001, 002, etc
which is formatted as a Number field.

Combined together I would like them to read E01-001, E01-002 etc, but using
the above formula, the leading zeroes are missing from the Reference Number
so it looks like E01-1.

Could someone please shed a bit of light where I'm going wrong.

Many thanks

Chris

 

[John Spencer]

[CostCentre] & "-" & FORMAT([ReferenceNumber],"000")

You need to use format to force the leading zeroes since the referencenumber
is data of type number. Numbers do not have leading (or trailing) zeroes when
they are stored. Text strings do.

The format command converts the number to a string of number characters and at
the same time applies a format to the display of the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[hobbit2612 via AccessMonster.com]

John,

That's great,

Many thanks for taking the time to reply.

Kind regards

Chris

[CostCentre] & "-" & FORMAT([ReferenceNumber],"000")

You need to use format to force the leading zeroes since the referencenumber
is data of type number. Numbers do not have leading (or trailing) zeroes when
they are stored. Text strings do.

The format command converts the number to a string of number characters and at
the same time applies a format to the display of the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

[quoted text clipped - 18 lines]

Chris