Thread: Format a Number Without Decimal

1. Registered User
Join Date
Aug 2004
Posts
173

Unanswered: Format a Number Without Decimal

I have the following calculation I need to format

GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])

the total comes out to be 11368.71

I need to remove the decimal and format it to look like this

1136871

When I try the format function, it rounds the number to 11369

How can I do this?

Thanks - AB

2. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
Try this:

Code:
```GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
GrossWages_Q4: (Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4)))```
.

3. Registered User
Join Date
Aug 2004
Posts
173

Works - but 1 More Issue..

Originally Posted by CyberLynx
Try this:

Code:
```GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
GrossWages_Q4: (Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4)))```
.

The above works well - except the data has some fields without a decimal - the ones with even money

example if the value is 11368.71 - it formats to 1136871 - fine!
but if the value is 1359 - then I get an error

some of the data has decimals, and some does not - this is a weird import, as it imports data with a decimal if the last 2 values are > .00 otherwise it just imports the value...

any ideas? you got me further ahead than I was...

Thanks! - AB

4. Registered User
Join Date
Aug 2004
Posts
173

pic attached

Here is a pic of the data - the testQ4 is the field with the code above...works, but not for all records

AB
Last edited by abinboston; 11-10-05 at 23:52. Reason: wrong pic

5. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
Try this:

Code:
```GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
GrossWages_Q4: (IIF(Instr(GrossWages_Q4, ".") > 0, Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4))), GrossWages_Q4)```
.

6. Registered User
Join Date
Aug 2004
Posts
173

Almost.....

Originally Posted by CyberLynx
Try this:

Code:
```GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
GrossWages_Q4: (IIF(Instr(GrossWages_Q4, ".") > 0, Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4))), GrossWages_Q4)```
.
Thanks for all your help - this is almost working.
The pic attached shows what is happenning.

in the data, some values do not have a decimal for example 1359

I need this to show as 135900

Any ideas? I am playing with it now.

Thanks again, - AB

7. Registered User
Join Date
Aug 2004
Posts
173

one last issue

there is also one more issue.

some of the data shows up as 6923.1 - eliminating the trailing 0

I need this to show as 692310

Thanks - AB

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
=int(myvalue*100)

or possibly
=fix(myvalue*100)

9. Registered User
Join Date
Aug 2004
Posts
173

one last issue

there is also one more issue.

some of the data shows up as 6923.1 - eliminating the trailing 0

I need this to show as 692310

Thanks - AB

10. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
maybe you have accidentally set the ignore flag on healdem's posts so i repeat what he said:

what's wrong with *100 ?
as in
=int(myvalue*100)
or possibly
=fix(myvalue*100)
izy

11. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
abinboston...it doesn't get any easier than that:

GrossWages_Q4 = Int(GrossWages_Q4 * 100)

.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•