424,949 Members | 884 Online
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
5 Replies

 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

 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 Left(Format([Account],"000000000"),8) Mary Mar 14 '07 #4

 P: 84 Try this ... Expand|Select|Wrap|Line Numbers Left(Format([Account],"000000000"),8) Mary GREAT!. That worked. Thank you :) Mar 14 '07 #5

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