-0,01 becomes 91 ???

[Frank]

On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I also
experimented with notation and the number of decimals.
Help is very appreciated,
Frank

 

[Tony]

Sounds like a date problem - 01 turning into 91
as to how to fix it, your guess is as good as mine, sorry

Tony

 

[Bruce M. Thompson]

On a cheque I want to split a number in its whole value

and the 2 decimals. I.e. ? 2,34 becomes 2 and in another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting and we may be able to help.

 

[Frank]

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank

 

[Ken Snell]

To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank

-----Original Message-----

Post the method by which you are doing the splitting and we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)
within the newsgroups so that all might benefit.<<


.

 

[Ken Snell]

Frank -

After rereading your post, I believe that there is something else going on
in your code that is causing the right-hand number to change from 01 to 91
in your situation. Can you post the entire code that is manipulating the
"numbers"? The example that I gave you below may not be what you seek at
all.

--
Ken Snell
<MS ACCESS MVP>

To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank

-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting and we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

 

[Frank]

With VT_TotInc=-0,01 the expression =Sgn([VT_TotInc])*CInt
(Right(CStr([VT_TotInc]);2)) results in -3 And I don't
know why... :-(
Frank

-----Original Message-----
To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr ([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank

-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting

and
we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

.

 

[Ken Snell]

Frank - as I noted in my most recent post, there is more going on here than
you've posted. Post the rest of the code so that we can see how this might
be happening.

--
Ken Snell
<MS ACCESS MVP>

With VT_TotInc=-0,01 the expression =Sgn([VT_TotInc])*CInt
(Right(CStr([VT_TotInc]);2)) results in -3 And I don't
know why... :-(
Frank

-----Original Message-----
To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr ([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank


-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in
another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I
also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting and
we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

.

 

[Frank]

OK
SO, it's report that makes bills to customers
In the detail there's a number called Tot_Inc, which is
the price of that order-line.
The total VT_TotInc of the bill is calculated and printed
in the footer of the detail-section with VT_TotInc=Som
([TotInc]), a currency with 2 decimal places. This
calculation is always ok, also with negative numbers.
On another part of the bill, the page-footer, this total
has to be split in an integer part and a decimal part to
be shown on different places on the bill. I calculated
the integer part with:
=Fix([VT_TotInc]), fixed notation, 2 decimals
The decimal part with:
=Right(CStr([VT_TotInc]*100);2), fixed notation, 0
decimals
And that goes wrong, if only with decimal numbers.
There's no more coding or calculaing than that.
As I said, I tried lots of things. I can see that in the
translation to string this strange 9 appears, because
when I use for the decimal-part only =CStr([VT_TotInc]) ,
there appears -9,99999999999091E-3, so the thing is
rounding numbers in a way I do not want at all. I just
want to see the two decimals that I see also in the first
total Som([TotInc])
Thanks for your help!

Frank

-----Original Message-----
Frank -

After rereading your post, I believe that there is something else going on
in your code that is causing the right-hand number to change from 01 to 91
in your situation. Can you post the entire code that is manipulating the
"numbers"? The example that I gave you below may not be what you seek at
all.

--
Ken Snell
<MS ACCESS MVP>

To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr ([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank


-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in
another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I
also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting and
we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

.

 

[Frank]

I did!
Frank

-----Original Message-----
Frank - as I noted in my most recent post, there is more going on here than
you've posted. Post the rest of the code so that we can see how this might
be happening.

--
Ken Snell
<MS ACCESS MVP>

With VT_TotInc=-0,01 the expression =Sgn([VT_TotInc]) *CInt
(Right(CStr([VT_TotInc]);2)) results in -3 And I don't
know why... :-(
Frank

-----Original Message-----
To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr ([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>


I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank


-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in
another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I
also
experimented with notation and the number of decimals.

Post the method by which you are doing the

splitting
and

we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.



.

.

 

[Frank]

Alas,
-0,01 , the 01 becomes a nice 01
But -1,20 the 20 becomes 99
???
Frank
Still very much apreciating your attention

-----Original Message-----

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.

***I see that Ken Snell beat me, but I'll post this

I'm not sure how you are getting the "91" out of "01" (actually "-1"), but
consider the following instead.

For the decimal part (to get "01" from "-0.01"), try:

Format(Right([VT_TotInc]; 2); "00")

If you need the sign, then use (Ken & I sometimes think alike):

Format(Sgn([VT_TotInc]) * Right([VT_TotInc]; 2); "00")

I am assuming, reading your example, that your regional settings require a ","
as a decimal (as opposed to "." in the US) and ";" as an argument separator in
function calls (as opposed to "," in the US).

Keep in mind, too, that the field the resulting value is being stored in will
need to be a text field, not numeric, or "01" will turn back into "1".

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.

 

[Ken Snell]

Ahhhh....now I better understand what is happening. Your code is not
properly "rounding" the inexact number that ACCESS is storing for the value.

Let's try this for the "right-hand" part of the number.

RightHandNumber = Format(Sgn([VT_TotInc]) *
CInt(Right(Format(CInt([VT_TotInc]*100), "00"), 2)), "00")

--
Ken Snell
<MS ACCESS MVP>

OK
SO, it's report that makes bills to customers
In the detail there's a number called Tot_Inc, which is
the price of that order-line.
The total VT_TotInc of the bill is calculated and printed
in the footer of the detail-section with VT_TotInc=Som
([TotInc]), a currency with 2 decimal places. This
calculation is always ok, also with negative numbers.
On another part of the bill, the page-footer, this total
has to be split in an integer part and a decimal part to
be shown on different places on the bill. I calculated
the integer part with:
=Fix([VT_TotInc]), fixed notation, 2 decimals
The decimal part with:
=Right(CStr([VT_TotInc]*100);2), fixed notation, 0
decimals
And that goes wrong, if only with decimal numbers.
There's no more coding or calculaing than that.
As I said, I tried lots of things. I can see that in the
translation to string this strange 9 appears, because
when I use for the decimal-part only =CStr([VT_TotInc]) ,
there appears -9,99999999999091E-3, so the thing is
rounding numbers in a way I do not want at all. I just
want to see the two decimals that I see also in the first
total Som([TotInc])
Thanks for your help!

Frank

-----Original Message-----
Frank -

After rereading your post, I believe that there is something else going on
in your code that is causing the right-hand number to change from 01 to 91
in your situation. Can you post the entire code that is manipulating the
"numbers"? The example that I gave you below may not be what you seek at
all.

--
Ken Snell
<MS ACCESS MVP>

To get the negative numbers to work, try this:

RightHandNumbers = Sgn([VT+TotInc]) * CInt(Right(CStr ([VT_TotInc]))).

--
Ken Snell
<MS ACCESS MVP>


I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank


-----Original Message-----
On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in
another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I
also
experimented with notation and the number of decimals.

Post the method by which you are doing the splitting and
we may be able to help.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

.

 

[Rolf Østvik]

I get the dicimal part with =Right(CStr([VT_TotInc]
*100);2) where VT_TotInc is the number (example: 3,23)
and the whole part with =Fix([VT_TotInc]), last one with
0 decimals.
With negative numbers this goes wrong.
Thanks for any help!
Frank

Try
= Right(CStr(abs([VT_TotInc])*100);2)
for the decimal part.

If you nned a leading zero then use
= format(Right(CStr(abs([VT_TotInc])*100);2) ;2)

(the function abs get the absolute value, which is it seems that you want.

 

[Bruce M. Thompson]

Alas,

-0,01 , the 01 becomes a nice 01
But -1,20 the 20 becomes 99
???
Frank
Still very much apreciating your attention

It seems that Rolf Østvik has come the closest. Try:

=Format(Right(Abs([VT_TotInc]*100)); 2); "00")

I hope this works for you.

 

[Marshall Barton]

On a cheque I want to split a number in its whole value
and the 2 decimals. I.e. ? 2,34 becomes 2 and in another
field 34. All is fine, exept with negative numbers.
-0,01 becomes 0 (OK) but the other part (01) in the
decimals-field shows 91. I tried to fix it with right,
mid, instr, fix... nothing works ok all the time. I also
experimented with notation and the number of decimals.

Like Ken said, there is something else going on here? At
one point, Frank said that this is a currency value, but
somewhere else he said that the value -0.01 displayed as
-9,99999999999091E-3, which shouts Double, not currency.

Since we are not guaranteed that there are only two digits
in the fractional part of the value, I think
Right(VT_TotInc)
is working on an unknown part of the number (hence the
result of 91). Here's what I came up with:

=Right(Format(VT_TotInc, "0.00"), 2)

The Format function will do the rounding and trim the value
to two digits of fractional part so that Right can work on
the desired digits.

 

[Bruce M. Thompson]

Since we are not guaranteed that there are only two digits

in the fractional part of the value, I think
Right(VT_TotInc)
is working on an unknown part of the number (hence the
result of 91). Here's what I came up with:

=Right(Format(VT_TotInc, "0.00"), 2)

The Format function will do the rounding and trim the value
to two digits of fractional part so that Right can work on
the desired digits.

Thanks, Marsh. I think I had my eyes in the wrong sockets, because I had that
exactly backwards. <groan>