Question Calculated field problem

[Templarman]

Hi guys, I have a problem which I hope someone could solve?

I am using Access 2007 and on a form I have a calculated control which takes a number of people and divides this by 21. This is so postal labels can be produced; 21 to a sheet. The control source states;

=Round(Sum(([Category1]="C")*-1/21))

Access returns the value of 11.

C=50 - so therefore 50/21 = 2.380...

But 2 sheets of labels = (2*21=42) is obviously not enough, being 8 short.

I know I need to alter something in the control source's syntax but I am truly stumped. :banghead:

Thanks

 

[Anakardian]

Try this:

Int(-100 * [Sum(([Category1]="C")*-1/21)) / -100

 

[bob fitz]

IIf(50 Mod 21 = 0, 0, Int((50 / 21) + 1))
This returns 3, which is what I think you want.
I'm not sure what "Category1" or "C" are so I'm not sure haow to put these into the expression instead of "50"

 

[Templarman]

Thanks Anakardian and bob fitz for both of your replies. Unfortunately neither worked.

Anakardian: I had to change your formula slightly to =Int(-100*(Sum(([Category1]="C")*-1/21))/-100) - I changed your original [ to ( and added another at the end. Access accepted this but still only returns a value of 2.

Bob Fitz: Category1 is the field name I am querying and C is the value to count. I gave the value so I could explain the maths (see original post).

Thanks to both of you but would you have another look at it for me please? I need to solve this.

Many thanks

Templarman

UPDATE:

I messed around with Anakardian's formula and changed it to:

=Int(-100*(Sum(([Category1]="C")*1/21))/-100) and this returns a value of -3 - so getting close. Just need to make it a positive integer.

 

[bob fitz]

Try:
=IIf(Sum([Category1]="C") * Mod 21 = 0, 0, Int((Sum([Category1]="C") / 21) + 1))

 

[Templarman]

Hi there Bob,

Thanks for getting back to me on this one. I copied and pasted your code into the control source and got an error.

I am not sure where the problem lies.

Thanks btw Bob

Templarman

 

[jdraw]

YourRequiredNumber = -Int(-(YourTotal/21))

? - Int( - (50/21))
 3

 

[Templarman]

Thanks jdraw,

here is my final working solution based on your code

=-Int(-Sum([Category1]="C")*-1/21)

This works a treat. Many many thanks to all who posted help.

Templarman