How to Convert Autonumber to Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DevDaniel - 01 Oct 2007 16:11 GMT

I have: A form with FieldA as an autonumber.

I want: When a new record is created, FieldB will be populated with a
3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
136 -> 136).

Reply to this Message

Marshall Barton - 01 Oct 2007 17:18 GMT

>I have: A form with FieldA as an autonumber.
>
>I want: When a new record is created, FieldB will be populated with a
>3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
>136 -> 136).

Why? There is no reason to save the same information twice.
You can display the autonumber field with leading zeros
simply be formatting it.

Note that displaying an autonumber field to users is almost
always a really bad idea.

Signature

Marsh
MVP [MS Access]

Reply to this Message

DevDaniel - 01 Oct 2007 19:39 GMT

> I have: A form with FieldA as an autonumber.
>
> I want: When a new record is created, FieldB will be populated with a
> 3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
> 136 -> 136).

An alternative question is: How do you create a three-character text field
that auto-increments each time a new record is created?

Reply to this Message

Pieter Wijnen - 02 Oct 2007 01:47 GMT

Keep it Numeric & Use the Format Property to Display it

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Long
i = Nz(Access.DMax("MyID","MyTable"),0) +1
Me.MyID.Value = i
End Sub

HtH

Pieter

>> I have: A form with FieldA as an autonumber.
>>
[quoted text clipped - 5 lines]
> An alternative question is: How do you create a three-character text field
> that auto-increments each time a new record is created?

Reply to this Message