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

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

<(E-Mail Removed)> wrote in message
news:682d2f19-6fdf-4f0a-83b3-(E-Mail Removed)...
> 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

<(E-Mail Removed)> wrote in message
news:682d2f19-6fdf-4f0a-83b3-(E-Mail Removed)...
> 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")


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)