Let MS Access add specific text to a field

Question

I've created a database in Access. and I want in a specific field to make Access automatically converts a text to specific format.

For example according to what I really need. in that field each time I enter a five digit number (e.g 20650) the access show it like this "ICL00020650" (this is a transaction reference at the place I'm working for). instead of each time <g class="gr_ gr_539 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="539" id="539">Ii</g> enter the full text (ICL00020650), I need Access to convert any 5 digits number to that format 

Answer 1

One option is to make the field a Number (Long Integer) field and to set the Format property to

"ICL"00000000

Access will store the five-digit number 20650, but display it as ICL00020650.

If you really want the value to be stored as the text value ICL00020650, the field should be a Short Text field with field size 11.

In the form used to enter/edit the records, create an After Update event procedure for the text box bound to the field. Let's say the field and text box are named MyText.

Private Sub MyText_AfterUpdate()
    If IsNumeric(Me.MyText) Then
        Me.MyText = "ICL" & Format(Me.MyText, "00000000")
    End If
End Sub

The code will only convert numeric entries; it will leave entries that are text (because they already start with ICL) unchanged.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

 instead of each time <g class="gr_ gr_539 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="539" id="539">Ii</g> enter the full text (ICL00020650), I need Access to convert any 5 digits number to that format 

A couple of questions --

Where will the "ICL000" come from?  Is that a constant for ALL numbers?  Or does 450xx have another prefix?  

What is the data/information you posted shown in bold?  

---

Build a little, test a little

Answer 3

The text in bold is a glitch; it is part of the source code of the page that shouldn't have been displayed, but the forum software is rather buggy. So you can ignore that part.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

Hi Shenoda.nabil,

I can see that after creating this thread, you did not follow up this thread again.

this thread is still open.

If your issue is solved now then I suggest you to update the status of this thread and post the solution here and mark it as an answer.

if the issue is still exist then I suggest you to refer the suggestion provided by the community members. it can solve your issue.

if still you have any further question regarding the above question then let us know about that.

we will try to provide further suggestion to solve the issue.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].