lookup

  • Thread starter Thread starter miguel
  • Start date Start date
M

miguel

Hi,
I have a form based on a query

4 fields in the form
field1 = text
field2 = text
field3 = numeric
field4 = calculates acording to the data of previous
example if field1 = "a" and field2 = "b" and field3 < 100
then .....
but if however in the form there are more records were
field1 and field2 are the same i want to be able to take
the sum of all these field3's in that case the value might
exceed 100 and a different calculation should be made

all help welcome
Miguel.
 
I'm not sure why you are doing this. This type of comparison seems very
odd, but if you must here is something that should work. Make another query
where you group by Field1 and Field2 and sum Field3. Then in your original
query add the sum of Field3 by linking Field1 and Field2. Then in your
calculation on the form, insteaf of looking at field3 look at the new
sumofField3.

IIF([field1]="a" and [field2]="b",
IIF([sumofField3]<100,CalcExpression1,CalcExpression2),ElseCalc)

Where

CalcExpression1 is the calc if sumofField3 < 100, CalcExpression2 is if
=100, ElseCalc is for if [field1] does not = "a" or if [field2] does not =
Click to expand...
"b". Goodluck.

Kelvin
 
Back
Top