Data Type - Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have numeric values stored in a field called "Starting Target". Some numbers have decimals (765.24) and some don't (765). A "Starting Target" is considered recent if it does NOT have decimals. I thought a function such as Right([Starting Target],2) would give me the 2 digits after the decimal, then if those digits are 00 I could label those as recent

Unfortunately, for a field that is displayed as 765.00, the Right function is giving me 65, not the 00 I was hoping for. Also, for a field displayed as 765.30, the Right function gives me .3, not 30. It seems to ignore all zeros after the decimal

I've played around with different Data Types and Formats, but so far no luck. Any suggestions?
 
Maybe you could try converting to a string in VB (or text
in the database) and then use the RIGHT() function
-----Original Message-----
I have numeric values stored in a field called "Starting
Click to expand...
Target". Some numbers have decimals (765.24) and some
don't (765). A "Starting Target" is considered recent if
it does NOT have decimals. I thought a function such as
Right([Starting Target],2) would give me the 2 digits
after the decimal, then if those digits are 00 I could
label those as recent.
Unfortunately, for a field that is displayed as 765.00,
Click to expand...
the Right function is giving me 65, not the 00 I was
hoping for. Also, for a field displayed as 765.30, the
Right function gives me .3, not 30. It seems to ignore
all zeros after the decimal.
I've played around with different Data Types and Formats,
Click to expand...
but so far no luck. Any suggestions?
 
Converting to text doesn't work. Somehow, I need the field to be stored as 765.00, not just 765. Even when converting to text, the zero's drop off.
 
Kirk P. said:
I have numeric values stored in a field called "Starting Target".
Some numbers have decimals (765.24) and some don't (765). A
"Starting Target" is considered recent if it does NOT have decimals.
I thought a function such as Right([Starting Target],2) would give me
the 2 digits after the decimal, then if those digits are 00 I could
label those as recent.

Unfortunately, for a field that is displayed as 765.00, the Right
function is giving me 65, not the 00 I was hoping for. Also, for a
field displayed as 765.30, the Right function gives me .3, not 30.
It seems to ignore all zeros after the decimal.

I've played around with different Data Types and Formats, but so far
no luck. Any suggestions?
Click to expand...

If the field is stored as a numeric type, anything you do involving the
string-handling Right() function is going to cause a forced conversion
to string first, and if you don't specify the exact string format you're
going to get varied results depending on the exact value of the field.

Should the field be stored as a Number? If you're going to be doing
calculations with it, then yes; otherwise, just store it as a text
field, and then no conversions will be involved and the Right() function
will be operating on exactly what was entered.

If the field *is* stored as a number, then you can do things like the
calculated-field definitions below:

IsRecent: (Int([Starting Target]) = [Starting Target])

DecimalsOnly: [Starting Target] - Int([Starting Target])

Depending on the actual data type of the field, you may need to build a
margin for imprecision into the definition of the IsRecent calculated
field; something like this instead:

IsRecent: (([Starting Target] - Int([Starting Target])) < .000001)

Another valid approach to this would be to force the number field to be
converted to a string representation with exactly 2 decimal places, and
then use your Right() expressions. For example:

IsRecent: (Right(Format([Starting Target], ".00"), 2) = "00")

DecimalsOnly: Right(Format([Starting Target], ".00"), 2)
 
Converting to text doesn't work. Somehow, I need the field to be stored
as 765.00, not just 765. Even when converting to text, the zero's drop off.

Not if you convert it using the VBA Format function: Format(765.30,"Fixed")
returns the text string 765.30.

*OR* you could multiple your value by 100 and then compare the 2 rightmost
characters. One advantage of this would being able to use the Mod operator:
76530 Mod 100 returns 30 ("recent" values that had no decimal before
multiplying them times 100 will return 0).

Elementary school division is often stated as "number 2 goes into number 1
x number of times with y left over."
Mod returns y, "the leftovers".

Warning: Mod rounds its arguments to integers before returning a value so
you need to lose the decimals before using it. (i.e., 765.35 Mod 1 is the
same as 765 Mod 1: both correctly return 0 but that would be wrong for what
you are trying to accomplish).

Hope this helps,
--
George Nicholson

Remove 'Junk' from return address.


Kirk P. said:
Converting to text doesn't work. Somehow, I need the field to be stored
Click to expand...
as 765.00, not just 765. Even when converting to text, the zero's drop off.
 
Back
Top