Iif Statement

  • Thread starter Thread starter David Whitaker
  • Start date Start date
D

David Whitaker

Hope this doesn't confuse you
I have got a field in a query named [type]
Its contents are always a 1,2, or 3

There are 2 other fields with the names of [gallons] and [trimgallons]
example (this is what the datasheet looks like)
type gallons trimgallons
2 25 5
1 10
3 21
1 12 18
1 90 3
Question?
How could you combine the following to get the results?
Gallons + trimgallons(if type =1) 'result=133
Gallons + trimgallons(if type =2) 'result=30
Gallons + trimgallons(if type =3) 'result=21
 
Don't know if this is any help
If you paste the following SQL into a query it gives what
you want
SELECT Sum([trimgallons]+[gallons]) AS Expr1
FROM Table1
HAVING ((([Table1]![type])=2))
ORDER BY Sum([trimgallons]+[gallons]);


Change type as appropriate.
 
Hello David,
I think you can do a Group/Total query and then do an
expression to sum gallons and Trimgallons.
When you do the group/total this is how it should look
Type Gallons trimga Add another field and put the
expression Field4:Nz([Gallons])+Nz([TrimGal])
1 112 21
2 25 5
3 21
the Nz is so that you can add even if one field has a
blank.
Hope this helps.

Juan
 
I tried your SQL Statement, and I got an error. Any ideals as to where the
syntax is wrong.
I did put this in the area where you select what field you want to use in
the query(this maybe wrong)
Thanks for your efforts.
Peter Cole said:
Don't know if this is any help
If you paste the following SQL into a query it gives what
you want
SELECT Sum([trimgallons]+[gallons]) AS Expr1
FROM Table1
HAVING ((([Table1]![type])=2))
ORDER BY Sum([trimgallons]+[gallons]);


Change type as appropriate.
-----Original Message-----
Hope this doesn't confuse you
I have got a field in a query named [type]
Its contents are always a 1,2, or 3

There are 2 other fields with the names of [gallons] and [trimgallons]
example (this is what the datasheet looks like)
type gallons trimgallons
2 25 5
1 10
3 21
1 12 18
1 90 3
Question?
How could you combine the following to get the results?
Gallons + trimgallons(if type =1) 'result=133
Gallons + trimgallons(if type =2) 'result=30
Gallons + trimgallons(if type =3) 'result=21



.
Click to expand...
Click to expand...
 
Hope this doesn't confuse you
I have got a field in a query named [type]
Its contents are always a 1,2, or 3

There are 2 other fields with the names of [gallons] and [trimgallons]
example (this is what the datasheet looks like)
type gallons trimgallons
2 25 5
1 10
3 21
1 12 18
1 90 3
Question?
How could you combine the following to get the results?
Gallons + trimgallons(if type =1) 'result=133
Gallons + trimgallons(if type =2) 'result=30
Gallons + trimgallons(if type =3) 'result=21
Click to expand...

No IIF statement is needed.

Change your Query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). Group By Type, and in a vacant Field cell
type

Result: Sum([Gallons]) + Sum([trimgallons])
 
Well,
John I will have to hand it to ya!
Another Job Well Done

TFTE
David Whitaker
 
Back
Top