Concatenate string + Number, losing leading zeros

[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

 

[Gina Whipp]

Try Format([txtCourseNumber],"0000")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dirk Goldgar]

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

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")