Access 2007, Plz Help Expression won't calculate

[lowanam1]

I can't get this calculation to total. Please let me know if I have it entered incorrectly. Thank You!

Total: Round(([Total Hours]*48.55)+([TOTAL MATERIAL VALUE]*6.4)*(11.23+[SUB QUOTES])*(6.4*11.23),0)

 

[Kiwiman]

Howzit

What do you mean it doesn't calculate?

Have you got all the fields referenced correctly ( [Total Hours], [TOTAL MATERIAL VALUE] and [SUB QUOTES] )? Is the formula what you expect - it looks like it will get pretty big fairly quickly - which maybe what you want. Should the * (in red below) be +?

Round(([Total Hours]*48.55)+([TOTAL MATERIAL VALUE]*6.4)[COLOR="Red"]*[/COLOR](11.23+[SUB QUOTES])[COLOR="Red"]*[/COLOR](6.4*11.23),0)

The syntax itself will \ should get a result, subject to the field referencing.

 

[lowanam1]

I changed/added the tables/expressions to read like this instead and it still did not work : Yes the fields are referenced correctly. Am i missing comas??

Total: [Total Hours]*[tbl_BAEComp]![Rate]+[MATERIAL VALUE]*[tbl_BAEComp]![G&A Rate]*[tbl_BAEComp]![Award/Inc Fee]+[SUB QUOTES]*[tbl_BAEComp]![G&A Rate]*[tbl_BAEComp]![Award/Inc Fee]

Let me explain what I am trying to calculate. Maybe that will help. Any help is greatly appreciated.

Totals hours x Rate (48.55) + Material total x G&A (6.40) x Award Inc/Fee (11.23) + Sub quotes x G&A (6.40) x Award Inc/Fee (11.23) =

 

[spikepl]

What will help the most is that you define "did not work" - did you get error? wrong value? did your code not run?

For the future: never say" did not work" because that provides no clue to ppl who are not seeing what you are seeing. Always state what the code did deliver, and what you expected to get.

 

[lowanam1]

ok thanks by did not work I mean: The formula did not calculate the total. I expected to see a dollar value and the field is blank.

 

[spikepl]

if one of the fields is null then the formula will come up with nothing. for such fields you can use the NZ function - look it up

 

[lowanam1]

Ok I looked up the Nz function and learned that I needed to wrap my formulas with the Nz function because there are fields that are null. Now the expression looks like this and totals to 0. Which is not the total dollar value of the expression. did I wrap the Nz function wrong?? 0 is not the answer to the expression.....

Total: Nz([Total Hours])*Nz([tbl_BAEComp]![Rate])+Nz([MATERIAL VALUE])*Nz([tbl_BAEComp]![G&A Rate])*Nz([tbl_BAEComp]![Award/Inc Fee])+Nz([SUB QUOTES])*Nz([tbl_BAEComp]![G&A Rate])*Nz([tbl_BAEComp]![Award/Inc Fee])

 

[lowanam1]

I also entered the expression with the Nz function this way and got the result of 0 which is not the total dollar value of the expression.

Total: Nz([Total Hours],0)*Nz([tbl_BAEComp]![Rate],0)+Nz([MATERIAL VALUE],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0)+Nz([SUB QUOTES],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0)