IIF condition in calculated query

[samotek]

Can you help me build the following IIF condition in the calculated query:

the condition is that SumOfCartons should be = SumOfQuantity/pack, if pack is less than 6
and SumOfcartons should be = SumOfQuantity is pack is greater than 6


I have tried to build the following :


SumOfCartons: Int ((IIf([pack]<6 SumOfQuantity]/[pack];SumOfQuantity))


But Access does not accept this.I will be grateful if somebody help me correct my error

 

[PHV]

SumOfCartons: Int(IIf([pack]<6; SumOfQuantity]/[pack]; SumOfQuantity))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hap007]

Trying to do a test on a Summed field can give you problems. Your iif is attempting this.

So, a simple way to solve this is build 2 queries.

First query does the summing.

Second Query uses the first query as input, you can then test and do the iif test and calculation with no problem

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[samotek]

Thank youto all of you.I am testing now your proposals.

I get an syntax error with my code:

SumOfCartons: Int(IIf([pack]<6; SumOfQuantity]/[pack];SumOfQuantity))

Where the syntax error may be?

 

[gmmastros]

missing the left square bracket before the first SumOfQuantity.

SumOfCartons: Int(IIf([pack]<6; [red][[/red]SumOfQuantity]/[pack];SumOfQuantity))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Hap007]

Like George stated, you are missing several brackets and I use commas instead of semi-colons:

SumOfCartons: Int(IIf([pack]<6, [SumOfQuantity]/[pack],[SumOfQuantity]))

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[PHV]

and I use commas instead of semi-colons
It's a regional setting issue.
Be aware that not all Tek-Tippers are living in USA ;-)