Solved: Access update query formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

roblong

Thread Starter
Joined
Oct 17, 2008
Messages
33
Hi,

I have a table with a text field (call it "NbrCounter") that holds data in the following format:

Number1
Number2
.
.
Number15
.
.
Number124
.
etc...

I'm wondering if there is a way to write an update query that would strip off the numbers on the right and put them into a new number column.

In Excel I would simply write a function like =RIGHT(E6,LEN(E6)-6) which would strip off 6 characters starting from the left, leaving just the numbers. Is there anything similar I can do in Access?

Thanks
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,456
still have those functions and MID

mid = http://www.techonthenet.com/access/functions/string/mid.php
left = http://www.techonthenet.com/access/functions/string/left.php
right = http://www.techonthenet.com/access/functions/string/right.php
length = http://www.techonthenet.com/access/functions/string/len.php

so you should be able to do something the same
something like - dont have access in front of me

=mid([nbrcounter],7,len[nbrcounter]-6
so this should start at character 7 (the 1st part of the number and then take 6 away from the length of the string and so extract the number

sorry its not the exact syntax
 

roblong

Thread Starter
Joined
Oct 17, 2008
Messages
33
Thanks, etaf!

Using the "mid" function worked like a charm.

Best,

Robert
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,456
excellent - sorry i could type exact syntax - but glad it worked all ok
(y)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top