Results 1 to 9 of 9
  1. #1
    pjtessi9 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2019
    Posts
    12

    Question How to pad a zero in a text field with an update query

    In my database, I have a table named: Table1
    I have a text field named: Test

    The data in that field is like this: RAI-220101-001 (RAI-year-month-day-sequential number)
    I want to execute a query that will modify all the records in that table so the data looks like this: RAI-220101-0001

    I want a zero to be added after the 11th position (sequentiel number with 4 digits instead of 3)

    As an example, In my current table, I have values that range from RAI-220101-001 to RAI-221231-1500. So I have values with 3 and 4 digits after the 11th position.
    I want to be able to update all my data so that the values that have only 3 digits after the 11th position adds a zero at the 12th position and does not alter the data that already have 4 digits after the 11th position.

    So to resume


    the data shows RAI-220101-001 and RAI-221231-1155 and I want the update to display RAI-220101-001 and not change RAI-221231-1155 which already have 4 digits after the last -.
    test.accdb
    I want to do this using an append query...I guess!

    Thanks for you help :-)

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This would be much easier if those bits of data were stored separately.
    If they are, you can simply created a calculated field in a query to display the data in the desired format.
    This has the added benefit of always creating the reference from the data.

    So the $64,000 question is - do you have the source data, or just this field to work with?

    And what happens when the sequential number goes above 9999 ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    pjtessi9 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2019
    Posts
    12
    Hi Minty,
    The information RAI-yymmdd-seq (RAI-220101-001) was entered manually in a form.
    I have made changes to my database so those numbers are now generated automatically by increment of 1 and not typed manually anymore.

    There is no problem for the sequential number as it will never go beyond 1500. (0001 to 1500 max)

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    the data shows RAI-220101-001 and RAI-221231-1155 and I want the update to display RAI-220101-001

    I think you mean
    the data shows RAI-220101-001 and RAI-221231-1155 and I want the update to display RAI-220101-0001 ?

    You could just check for length, if short take the first 11 chars, add a "0" and the remaining 3 chars.
    If not short, just the value again?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay. This is obviously a one off adjustment so do it in stages

    Firstly create a query that isolates the "problem children"
    In the query window create a query as such

    Select Table1
    YourTable
    WHERE
    Len(Test) <= 15

    That should give you just the ones with a 3 digit or less last part?

    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    pjtessi9 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2019
    Posts
    12
    And how do I write that in a query???

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    From the query above you need to split from the second "-" in the [Testfield
    Assuming the remaining values are all simply missing the 0 then the whole query to test would be

    Select *, MyNewField: Left([Test],12) & "0" & Mid([Test,13)
    FROM YourTable
    WHERE
    Len(Test) <= 15

    If that works then change it to an update and move the MyNewField experssion to the replacement value

    UPDATE YourTable
    SET [Test] = Left([Test],12) & "0" & Mid([Test,13)
    WHERE Len([Test]) <=15

    All the above is air code (Untested)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    No need for update.

    Use the query below in place of the table and the RAICode field in place of test field.

    Code:
    SELECT Table1.*, Left([test],11) & Format(Mid([test],12),"0000") AS RAICode
    FROM Table1;

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Another way (on fly)
    Code:
    UPDATE YourTable 
    SET [Test] = LEFT(Test,11) & RIGHT("0000" & RIGHT([Test],LEN([Test]) - 11),4)
    

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-04-2019, 06:53 PM
  2. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  3. UPDATE QUERY to Separate Numbers from Text in a Text Field
    By pjordan@drcog.org in forum Queries
    Replies: 2
    Last Post: 05-29-2015, 02:44 PM
  4. Long Text field in an Update query
    By Dave D in forum Queries
    Replies: 2
    Last Post: 08-03-2014, 12:10 PM
  5. Replies: 2
    Last Post: 05-05-2010, 02:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums