none
Let MS Access add specific text to a field RRS feed

  • 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 

    Sunday, August 13, 2017 7:37 PM

All replies

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

    Sunday, August 13, 2017 8:31 PM
  •  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

    Sunday, August 13, 2017 8:59 PM
  • 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)

    Sunday, August 13, 2017 10:16 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 9:04 AM
    Moderator