Splitting numbers into units

[Guest]

I am producing a report of prize money won, and I have a total. I now want
to split this into units, tens, hundreds, thousands etc so that these values
can be put in the correct place for printing on pre-printed cheques.

Thanks

 

[Duane Hookom]

You might want to provide some sample values and expected output. I think
you can just convert the numbers to strings with Str([YourField]) and then
use Mid(). Check Help for specifics on these functions.

 

[Guest]

Thanks Duane you have helped me find a possible solution - but it is messy
and I would welcome some "clean up" suggestions...

First some sample values - well my report (one page per customer) has a
[totalamount] on it and this could be anything from £15.00 to eg £123456.00.
I now want to split the numbers down so that I have the number of units, the
number of tens, the number of thousands etc. So that the individual numbers
can be split into separate boxes to be printed out - in the simple case above
I want the result of [tens] = 1 and [units] =5.

To get the units is easy - =Right(Int([amount]),1). (I'm not interested in
anything after the decimal, so I've converted to integer)


After that my solution is to take the 2 numbers from the right and then the
left hand most one of these ie
[hundreds]=Right(I[amount],2)
=left([hundreds]),1)

and so on - at each step taking one more from the left. This is very long
winded and I feel perhaps I should now move into VB.

Unless you have any better ideas?

MAny thanks

You might want to provide some sample values and expected output. I think
you can just convert the numbers to strings with Str([YourField]) and then
use Mid(). Check Help for specifics on these functions.

--
Duane Hookom
MS Access MVP

I am producing a report of prize money won, and I have a total. I now want
to split this into units, tens, hundreds, thousands etc so that these values
can be put in the correct place for printing on pre-printed cheques.

Thanks

 

[Duane Hookom]

I would first format all values to 6 characters
Right(Space(6) & [totalamount]\1,6)
Then you can use the mid() function to grab any place ie:
=Mid(Right(Space(6) & [totalamount]\1,6) , 3,1)

--
Duane Hookom
MS Access MVP
--

Thanks Duane you have helped me find a possible solution - but it is messy
and I would welcome some "clean up" suggestions...

First some sample values - well my report (one page per customer) has a
[totalamount] on it and this could be anything from £15.00 to eg £123456.00.
I now want to split the numbers down so that I have the number of units, the
number of tens, the number of thousands etc. So that the individual numbers
can be split into separate boxes to be printed out - in the simple case above
I want the result of [tens] = 1 and [units] =5.

To get the units is easy - =Right(Int([amount]),1). (I'm not interested in
anything after the decimal, so I've converted to integer)


After that my solution is to take the 2 numbers from the right and then the
left hand most one of these ie
[hundreds]=Right(I[amount],2)
=left([hundreds]),1)

and so on - at each step taking one more from the left. This is very long
winded and I feel perhaps I should now move into VB.

Unless you have any better ideas?

MAny thanks

You might want to provide some sample values and expected output. I think
you can just convert the numbers to strings with Str([YourField]) and then
use Mid(). Check Help for specifics on these functions.

--
Duane Hookom
MS Access MVP

I am producing a report of prize money won, and I have a total. I now want
to split this into units, tens, hundreds, thousands etc so that these values
can be put in the correct place for printing on pre-printed cheques.

Thanks

 

[Guest]

Thanks that works well - but could you please explain your first step

Right(Space(6) & [totalamount]\1,6)

Particularly the \1

Thanks again

I would first format all values to 6 characters
Right(Space(6) & [totalamount]\1,6)
Then you can use the mid() function to grab any place ie:
=Mid(Right(Space(6) & [totalamount]\1,6) , 3,1)

--
Duane Hookom
MS Access MVP
--

Thanks Duane you have helped me find a possible solution - but it is messy
and I would welcome some "clean up" suggestions...

First some sample values - well my report (one page per customer) has a
[totalamount] on it and this could be anything from £15.00 to eg £123456.00.
I now want to split the numbers down so that I have the number of units, the
number of tens, the number of thousands etc. So that the individual numbers
can be split into separate boxes to be printed out - in the simple case above
I want the result of [tens] = 1 and [units] =5.

To get the units is easy - =Right(Int([amount]),1). (I'm not interested in
anything after the decimal, so I've converted to integer)


After that my solution is to take the 2 numbers from the right and then the
left hand most one of these ie
[hundreds]=Right(I[amount],2)
=left([hundreds]),1)

and so on - at each step taking one more from the left. This is very long
winded and I feel perhaps I should now move into VB.

Unless you have any better ideas?

MAny thanks

You might want to provide some sample values and expected output. I think
you can just convert the numbers to strings with Str([YourField]) and then
use Mid(). Check Help for specifics on these functions.

--
Duane Hookom
MS Access MVP


I am producing a report of prize money won, and I have a total. I now
want
to split this into units, tens, hundreds, thousands etc so that these
values
can be put in the correct place for printing on pre-printed cheques.

Thanks

 

[Duane Hookom]

[totalamount]\1 is the same as Int([totalamount]) (at least for positive
numbers). It just truncates the decimal.
Space(6) returns six space characters so your numeric values get converted
to:
" 15"
"123456"


--
Duane Hookom
MS Access MVP
--

Thanks that works well - but could you please explain your first step

Right(Space(6) & [totalamount]\1,6)

Particularly the \1

Thanks again

I would first format all values to 6 characters
Right(Space(6) & [totalamount]\1,6)
Then you can use the mid() function to grab any place ie:
=Mid(Right(Space(6) & [totalamount]\1,6) , 3,1)

--
Duane Hookom
MS Access MVP
--

Thanks Duane you have helped me find a possible solution - but it is messy
and I would welcome some "clean up" suggestions...

First some sample values - well my report (one page per customer) has a
[totalamount] on it and this could be anything from £15.00 to eg £123456.00.
I now want to split the numbers down so that I have the number of

units,
the

number of tens, the number of thousands etc. So that the individual numbers
can be split into separate boxes to be printed out - in the simple

case
above

I want the result of [tens] = 1 and [units] =5.

To get the units is easy - =Right(Int([amount]),1). (I'm not

interested
in

anything after the decimal, so I've converted to integer)


After that my solution is to take the 2 numbers from the right and

then
the

left hand most one of these ie
[hundreds]=Right(I[amount],2)
=left([hundreds]),1)

and so on - at each step taking one more from the left. This is very long
winded and I feel perhaps I should now move into VB.

Unless you have any better ideas?

MAny thanks
:

You might want to provide some sample values and expected output. I think
you can just convert the numbers to strings with Str([YourField])

and
then

use Mid(). Check Help for specifics on these functions.

--
Duane Hookom
MS Access MVP


I am producing a report of prize money won, and I have a total. I now
want
to split this into units, tens, hundreds, thousands etc so that these
values
can be put in the correct place for printing on pre-printed cheques.

Thanks

 

[Fons Ponsioen]

What about a query like:
SELECT Ally.Qty, Right(Int([QTY]),1) AS Ones, Right(Int
([QTY]/10),1) AS Tens, Right(Int([QTY]/100),1) AS
Hundreds, Right(Int([QTY]/1000),1) AS Thousand, Right(Int
([QTY]/10000),1) AS TenThousand, Right(Int
([QTY]/100000),1) AS HundredThousand
FROM Ally;
I used a table named "Ally" and I used the field "Qty"
Fons