Help needed working with numbers

[sesling]

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?

[MMcCarthy]

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

[sesling]

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?

[MMcCarthy]

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 ...

Left(Format([Account],"000000000"),8)

Mary

[sesling]

Try this ...

Left(Format([Account],"000000000"),8)

Mary

GREAT!. That worked. Thank you :)

[MMcCarthy]

GREAT!. That worked. Thank you :)

You're welcome.