query syntax

[ivor]

I'm having problems with the following syntax in a query. On trying to run the query, I get a message "The expression you entered has a function containing the wrong number of arguments."



Reqd:IIf(Sum([Qty]*(Sum([GroupQty])>Int(Sum(Qty)*(SumGroupQty)),Int((Sum(Qty)*(SumGr oupQty))+1),Int(Sum(Qty)*(SumGroupQty)))))

Can someone please help?

[rpeare]

Does your query have a 'pre' query? I'm asking because you have 'sumgroupqty' which is similar to the default label of 'sumofgroupqty' that's assigned on a summation query.

Try this:

Reqd:IIf(Sum([Qty]*[GroupQty]) > Int(Sum([Qty]*[GroupQty])),Int(Sum([Qty]*[GroupQty])+1) ,Int(Sum([Qty]*[GroupQty])))

[pradeep.sands]

I guess the problem is with the braces...
the format of IIf is:

IIf(condition,what to do if true, what to do if false)

follow that exctly and end the braces at their exact positions...if you end some braces at the end of the entire command then it says wrong number of arguments.

[euanl]

At first glance there appears to be several missing brackets, brackets in the wrong place and wrong bracket types. It's not 100% clear what your field names are. It would also help if you tell me what calculation you are trying to make. Supply me with that info and I will see what I can do to fix it.

[rpeare]

He's trying to round a calculation upward for any value over an integer i.e. 1.0000000001 is rounded to 2

[ivor]

There is no 'pre'query. The information comes from a table.
What I'm trying to achieve is this; If Qty*GroupQty is greater than the integer of Qty*GroupQty, then add one to the integer of Qty*GroupQty, else use Qty*GroupQty.
Example of what I want: 4(Qty)*3.1(GroupQty)=12.4 Integer of 12.4 is 12, which means Qty*GroupQty is greater than its integer, so add one to the integer (12+1) to get a result of 13.
4(Qty)*3.0(GroupQty)=12.0 Integer of 12.0 is 12, which means Qty*GroupQty is the same as its integer, so the result is 12.

What I get when I run the query with your suggestion is as follows:

SumOfGroupQty	SumOfQty	Reqd
8	0.2	1
8	2	8
8	4	16
4	2	4
12	4	12
12	28	84
12	8	24
8	0.2	1
12	28	84
12	1	3
4	6	12

I had the query running earlier with, I think, similar code and got similar results.

[ivor]

What I'm trying to achieve is this; If Qty*GroupQty is greater than the integer of Qty*GroupQty, then add one to the integer of Qty*GroupQty, else use Qty*GroupQty.
Example of what I want: 4(Qty)*3.1(GroupQty)=12.4 Integer of 12.4 is 12, which means Qty*GroupQty is greater than its integer, so add one to the integer (12+1) to get a result of 13.
4(Qty)*3.0(GroupQty)=12.0 Integer of 12.0 is 12, which means Qty*GroupQty is the same as its integer, so the result is 12.

[ivor]

Yes, but 1.0000000000 is 1.

[rpeare]

The formula I posted should work.

[euanl]

I made a dummy table, ran a dummy query. This appears to work, give it a go::

Reqd: IIf([Qty]*[GroupQty]>Int([Qty]*[GroupQty]),Int([Qty]*[GroupQty])+1,Int([Qty]*[GroupQty]))