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

x
Solved

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

Posted on 2011-10-07
Medium Priority
294 Views
For example this is how it looks now: 302859
I want it to look like: 3020859

0
Question by:donnie91910
• 3
• 2

LVL 74

Accepted Solution

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

LVL 74

Expert Comment

ID: 36932434
for numeric values...

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

LVL 61

Expert Comment

ID: 36932460
Set yourfield=left (Yourfield,Len(yourfield)/2) & "0" & right(yourfield,Len(youtfield)/2)
0

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
0

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

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:

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

0

## Featured Post

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…
###### Suggested Courses
Course of the Month17 days, 17 hours left to enroll