Proper use of Round function

Topic

New Reply

Using Access 2007

On a form, I have a text box that pulls the total of Materials ordered from a subform fsubMaterials. The Materials are then marked up by the contractor, with a default of 20% but the contractor can change this value in a text box called txtMarkup.

Is this the proper way to do that Round function?

Code:

=Round([txtMarkup]*Nz([fsubMaterials].[Form]![txtMaterials],2),2)

The value is then added to the total Materials cost to produce a TotalMaterialsCost, which has the following construction.

Code:

=Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+[txtMarkupAmt]

Perhaps there is a way to do this in one step and I haven’t played with that yet. My main concern is getting the Round properly.

Tom

Reply | Quote

Replies

Tom,

The round function has 2 arguments
1. Value to round {can be a formula}
2. Number of decimal places to round

So the proper format is =Round(something that results in a number, number of places to round)

You say the markup can be changed but my question is can the change the percentage or the amount?
If they are changing the percentage you don’t want to add that amount.

Note: if number of places to round is negative it rounds to the left, i.e. -2 would round to the nearest 100.
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG
Thanks for the input.

To clarify, here are the steps.
1. The user input the varied Materials in fsubMaterials
2. The text box in the footer of fsubMaterials sums the cost of those Materials. That text box is called txtMaterials
3. On fsubProjects, the txtMarkup text box has a default of 20% but that percentage can be modified by the user.
4. On fsubProjects, a hidden text box called txtMarkupAmt calculates the amount of the Markup as follows:

Code:

=Round([txtMarkup]*Nz([fsubMaterials].[Form]![txtMaterials],2),2)

5. On fsubProjects, another text box called TotalMaterialsCost takes the value from txtMarkupAmt and adds it to the value from txtMaterials, as follows:

Code:

=Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+[txtMarkupAmt]

Tom

Reply | Quote

You could have avoided using the hidden text box by using
=Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+ (1 + [txtMarkupAmt])

BTW, are you aware that if [fsubMaterials].[Form]![txtMaterials] has a null value, then Nz([fsubMaterials].[Form]![txtMaterials],2) will result in a value of 2?

Cronk

Reply | Quote

Thanks, Cronk. Yes, I see what you mean by the misplacement of the “2”

Tom

Reply | Quote