Solved

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

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

0
Question by:donnie91910

LVL 73

Accepted Solution

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

LVL 73

Expert Comment

for numeric values...

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

LVL 61

Expert Comment

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

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
0

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

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:

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

0

Featured Post

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…