Concatenate string + Number, losing leading zeros

  • Thread starter Thread starter bakerstreet
  • Start date Start date
B

bakerstreet

Hello,

I am working on an Access 2007 database. I have a form in this
database with a dropdown for a "topic", which is an alpha value, for
instance "a". I have a text field called "txtCourseNumber" where you
enter a numeric value, and then stores that value into a table. The
field in the table is set as a number, and the format property is set
as 0000

My goal is to concatenate these two fields into a third field called:
"txtNcode" When I attempt the concatenation, I lose any leading zeros
that are in the numeric field "code", even though when I look in the
table the associated cell is filled correctly with the zeros.

I have tried format() and format$() and Cstr() and none seem to give
me the result I am looking for. Here is the code I am using in VBA:


Dim nc As String

nc = cmbTopic.Value & "-" & txtCourseNumber
'I have tried this: format(txtCourseNumber) and format
(txtCourseNumber.value) and format$ for both those.
txtNCode.Value = nc

My end goal is to fill txtNcode with a-0001


Any help would be greatly appreciated!

-KB
 
Hello,

I am working on an Access 2007 database. I have a form in this
database with a dropdown for a "topic", which is an alpha value, for
instance "a". I have a text field called "txtCourseNumber" where you
enter a numeric value, and then stores that value into a table. The
field in the table is set as a number, and the format property is set
as 0000

My goal is to concatenate these two fields into a third field called:
"txtNcode" When I attempt the concatenation, I lose any leading zeros
that are in the numeric field "code", even though when I look in the
table the associated cell is filled correctly with the zeros.

I have tried format() and format$() and Cstr() and none seem to give
me the result I am looking for. Here is the code I am using in VBA:


Dim nc As String

nc = cmbTopic.Value & "-" & txtCourseNumber
'I have tried this: format(txtCourseNumber) and format
(txtCourseNumber.value) and format$ for both those.
txtNCode.Value = nc

My end goal is to fill txtNcode with a-0001
Click to expand...


The Format property of a field or control doesn't affect what is stored,
only how it is displayed. A Number field never stores leading zeros.
You'll need to format the field value with leading zeros as you concatenate
it into your string, like this:

nc = cmbTopic.Value & "-" & Format(txtCourseNumber, "000")
 
Back
Top