Update field data with a 0 in the middle of the data.

Posted on 2011-10-07
Last Modified: 2012-05-12
For example this is how it looks now: 302859
I want it to look like: 3020859

Thanks in advance.
Question by:donnie91910
    LVL 73

    Accepted Solution

    left(yourfield,3) + "0" + right(yourfield,3)
    LVL 73

    Expert Comment

    for numeric values...

    int(yourfield/1000) * 10000 + mod(yourfield,1000)
    LVL 61

    Expert Comment

    Update your table
    Set yourfield=left (Yourfield,Len(yourfield)/2) & "0" & right(yourfield,Len(youtfield)/2)
    LVL 73

    Expert Comment

    oops, my modulo syntax isn't correct for access

    int(yourfield/1000) * 10000 + (yourfield mod 1000)

    note,  I'm assuming your input will always be 6 digits here,  or 6 characters in my first post

    if it will vary, then you'll need something like mbizup posted that tries to derive it from the string length

    Author Comment

    Like this?
    Set [DO_9_DO].[Release]=left ([DO_9_DO].[Release],Len(Release)/2) & "0" & right([DO_9_DO].[Release],Len(Release)/2)
    LVL 61

    Assisted Solution

    Yes - you probably don't need all of the prefixes and brackets, though, and I assume you have the UPDATE clause:

    SET Release = left (Release,Len(Release)/2) & "0" & right(Release,Len(Release)/2)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now