Rounding Problems 2

[desmur]

I understand how to get numbers to show as rounded numbers but how can I use that rounded number in my calculation instead of the number with all the digits.

 

[Terpsfan]

This is a simple example of how to use round in a query. You need to alias the Rounded column, which here is Total. Then use that Alias for another calculation in your query.

SELECT tblDecimals.Number1, tblDecimals.Number2, Round([Number1]*[Number2]) AS Total, [Total]*5 AS GrandTotal
FROM tblDecimals;

 

[desmur]

I am not able to get that to work, let me explain what I need to do. I have a calculation in query of
[billrate] / [payrate]. This rate returns a value of multiple decimal places. I only want it to return two decimal places. I want it to just seem like it by changing the formatting to fixed with 2 decimal places, I need it to go ahead and round it to 2 decimals because I need to use this in another calculation

 

[Terpsfan]

I'm assuming you are using currency for billrate and payrate. CCur automatically formats it to 2 decimals.

CCur([billrate] / [payrate])

 

[desmur]

None of this worked, let me explain it better.

[payrate]/[billrate] in a query returns a value of .6875 but I need it to return a value of .69. Now it displays .69 because I set the format to fixed with 2 decimals but I need it to calculate to .69. Can you give me the syntax to do this and tell me where to put it. (sql, module, .....) thanks

 

[Terpsfan]

Sorry I haven't helped you so far. Did you try right-clicking the payrate/billrate calculated field in the query design grid? You then go to properties and you can set the decimal places there. Or in SQL you could do this:
Format( [billrate]/[payrate],"#0.00" )AS FinalRate

 

[lhite]

How about something like this:
CDbl(CLng([payrate]/[billrate]*100))/100

-Larry

 

[EM98]

Did you find a solution to this problem? I have a query that has a currency field. When I use the currency field in a calculation, I automatically get 4 decimal places. i.e. 28.8259. I've tried to set the format to currency, this seems to work because it displays $28.83. However the underlying value is still 28.8259.

This causes a problem because when I try to use this value in another calculation, it uses the underlying value. For example if the product price is $28.83 and I've sold 100 of them, I made $2883.00. Access does not calculate it that way. It returns a value of $2882.59 which is based on the underlying value which is not accurate.

I hope this makes sense. I am an access instructor and this question came up in an INTRO class so I'd like to avoid VB if at all possible. Please help!! Thanks.

 

[Atalla]

I am using a two fields calculation as an example. You can use the below formula to round up the value to 2 decimal places.

NewFieldName: CDbl(Format([Field1]/[Field2],"#.0&quot)