Trimming a double variable in VBA / SQL.

pwdells
pwdells used Ask the Experts™
on
Quite often, after allocating stock into orders, I get a remaining variable with an amount like this: 9.74100000000001  

How can I just trim the variable to just 9.741 and have it stored in the database field like that?

Thank you in advance!

Wendee
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
pwdells,

You can try using the round function, but that will be vulnerable to the vagaries of floating point arithmetic as well.

If you want to fix the decimal places for the stored values, I recommend using either the Currency or Decimal data types, instead of Double or Single.

Patrick

Author

Commented:
In the table field settings (design view)?

Wendee
HainKurtSr. System Analyst

Commented:
to update existing field, something like this:

update table set col=round(col,4)

you can use round(col, n) in your select statements as well...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2010

Commented:
pwdells,

Yes, that's right.

Please back up your data before attempting such a change, though :)

Patrick
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> .. a remaining variable with an amount ..

If you are handling amounts you should at once change any use of Double to Currency any place in your code and tables. If not, you will be bitten by the behaviour with tiny calculation errors - often when you least expect it.

/gustav

Author

Commented:
What I did was change the field types to decimal with Scale and Decimal Field attributes set to 3.  It is not a current field, it is bulk material weights.

Author

Commented:
Make sure you set the Decimal Places and Scale attributes to the number of places to the right, to ensure you don't lose data within your field.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial