Solved

Field rounding in Access 2007

Posted on 2013-02-04
8
491 Views
Last Modified: 2013-02-04
I have a formula that runs when the user clicks on a certain field on a form in Access 2007. I have the field set in the table as decimal, fixed, 2 decimal places. I have it set the same way in the form. This works fine if I enter the number but how do I fix this field that is connected to the formula. It keeps rounding to the nearest whole number and I need it to round out 2 decimal places.

I have also tried field = round(2,(field1 - field2)) and that gives me a debug error.
0
Comment
Question by:MRG_AL
  • 3
  • 3
  • 2
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38851643
You have the parameters switched around...

field = round((field1 - field2),2)

Also, you'll want to throw in Nz to handle any NULLS

field = round((Nz(Me.field1,0) - Nz(field2,0)),2)
0
 

Author Comment

by:MRG_AL
ID: 38851869
It is still rounding but is providing two zeros after the decimal point.

The round equation your provided did work in that it didn't give me an error but I can't get it to calculate out two decimal places still.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38851886
My guess is that you have the field in the underlying table defined as an integer.
0
 

Author Comment

by:MRG_AL
ID: 38851929
It is set up as a decimal on the table. I have tried double, long integer, and decimal. But I have it as Decimal, 2, fixed

That is what I don't understand. I can type in 2 digits after the decimal on a different field but this one runs through the equation and gives me two zeros everytime?!?!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 38851930
fyed would be correct if field1 and field2 are integer values.  It's also possible that something else is either reading it in as an integer, or the form properties are such that it's displaying as an integer.

>It is still rounding but is providing two zeros after the decimal point.
Try rounding separately.

field = Round(Nz(Me.field1,0),2) - Round(Nz(field2,0),2)
0
 

Author Closing Comment

by:MRG_AL
ID: 38852037
It was the Scale on the table. The scale has to be set to 2 and I only had the decimal places set to 2.

Thank you for all your help and advice. The formula works perfect with the rounding now that I have the ,2 at the end of the formula
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38852098
So, how does the solution you accepted relate to the scale on the table?

Actually, Access doesn't even have a "scale" property for numeric fields, what system is your backend in?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38852409
Thanks for the grade, although if this were a SQL Server linked table, with a scale of 0 as you say, then fyed should receive at least partial credit.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now