By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Help needed working with numbers

P: 84
I have a database field that stores 8 and 9 digit values. I need to calculate the sum value using the first 8 digits.

ex.of stored numbers
123456781
234567892
45678903
987654321
calculation should use
12345678
23456789
_4567890
98765432

My current query statement takes the first 8 of the 9 digit numbers and it takes all 8 of the eight digit numbers. Left(Account,8).

Is there a way to alter the statement so it takes the first 8 digits of the 9 digit numbers and inserts a leading 0 or blank space for the 8 digit numbers?
Mar 13 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I have a database field that stores 8 and 9 digit values. I need to calculate the sum value using the first 8 digits.

ex.of stored numbers
123456781
234567892
45678903
987654321
calculation should use
12345678
23456789
_4567890
98765432

My current query statement takes the first 8 of the 9 digit numbers and it takes all 8 of the eight digit numbers. Left(Account,8).

Is there a way to alter the statement so it takes the first 8 digits of the 9 digit numbers and inserts a leading 0 or blank space for the 8 digit numbers?
Format the numbers in the table design as

000000000

This will put a leading 0 on all numbers with only 8 digits.

Mary
Mar 14 '07 #2

P: 84
Format the numbers in the table design as

000000000

This will put a leading 0 on all numbers with only 8 digits.

Mary

Thx for the hint but I cannot change the table design. Other processes in our DB need the 8 and 9 digit numbers. Are there any other options?
Mar 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Format the numbers in the table design as

000000000

This will put a leading 0 on all numbers with only 8 digits.

Mary
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Left(Format([Account],"000000000"),8)
Mary
Mar 14 '07 #4

P: 84
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Left(Format([Account],"000000000"),8)
Mary

GREAT!. That worked. Thank you :)
Mar 14 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
GREAT!. That worked. Thank you :)
You're welcome.
Mar 14 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.