Expression for summing fields and multiplying not working-Access 2010

[jlr123]

My expression is not working for some reason. Can you assist?
[Tech1 Salary]+[Tech2 Salary]+[Tech3 Salary]+[Tech4 Salary]+[Tech5 Salary]+[Tech6 Salary]+[Tech7 Salary]+[Tech8 Salary]*[Salary Allocation %]
If there are blanks I went back in and entered 0.00.

Thanks.

 

[Andrzejek]

Do you get en error? If so, what's the error?
Is that a piece of an SQL? A piece of code?
If so, please provide the WHOLE SQL / Code

" is not working for some reason" is NOT an explanation of your problem :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Do you really have columns named like this? Your table(s) seem a bit un-normalized. Also, you do you understand only [Tech8 Salary] is being multiplied by the [Salary Allocation %]?

If you want to multiply the sum and there is a possibility of blanks, you can try:

(Nz([Tech1 Salary],0)+Nz([Tech2 Salary],0)+Nz([Tech3 Salary],0)+Nz([Tech4 Salary],0)+Nz([Tech5 Salary],0)+Nz([Tech6 Salary],0)+Nz([Tech7 Salary],0)+Nz([Tech8 Salary],0))*[Salary Allocation %]

Duane
Hook'D on Access
MS Access MVP

 

[jlr123]

Yes, I know it is a "bit" actually a little more than a "bit"un-normalized but it is what it is.The expression is not giving me the correct value. In one row I have eight columns(fields) with the Tech 1 thru 8 Salary. I want to add those together and multiply times Salary Allocation % column (field). I receive an error when trying to use the Nz code. The error states I cannot use it in a calculated field. I may not be copying it correctly. If you can put it in a message rather than in the Code Expression field I could try it again.

Thanks.

 

[dhookom]

Apparently you are attemting to create a calculated field which I typically consider bad practice. I always create calculations in queries and control sources. There are some expressions that are not allowed in calculated fields.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

You really need to answer the questions that Andy posed, above! Exactly how/where are you attempting to do this? This makes a difference in the syntax that has to be used.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[jlr123]

The calculation is in a field in my one table to calculate the sum of the Tech Salary fields. I realize that I can do this in a query and I will try that instead.

 

[missinglinq]

I know that the Boys of Redmond added the ability to create Calculated Fields at the table-level, starting with v2010, but I have seen no threads concerning their usage and hence don't know if they can be problematic, as new features often are. And just to qualify the 'I have seen no threads...' part, I've been in forced retirement for eight years, now, and typically spend 8-10 hours a day/7 days a week, cruising here and on four or five other Access forums.

Doing Calculated Fields in Queries is, of course, a tried and proved method, while doing so in Tables is not. The error message you got about not being able to use the Nz() function would seem to indicate that the latter method has restraints not present in Queries, as you can use Nz()against all Fields, including Calculated Fields, in Queries, as Duane's code indicated. I would definitely go that route!

Good luck!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!