[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-07
Medium Priority
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
  • 3
  • 2
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 36932425
left(yourfield,3) + "0" + right(yourfield,3)
LVL 74

Expert Comment

ID: 36932434
for numeric values...

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

Expert Comment

ID: 36932460
Update your table
Set yourfield=left (Yourfield,Len(yourfield)/2) & "0" & right(yourfield,Len(youtfield)/2)
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 74

Expert Comment

ID: 36932477
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

ID: 36932661
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

mbizup earned 1000 total points
ID: 36932799
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)


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

831 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