IF Statement

[awalker]

Hi,
I am trying to run a query that has 4 fields: location, type, year, and
cost. However I need to increase the cost for the year 2007 for 2 types.
The IF statement I entered into the cost field is:

TotalCost:IIF([Type]="C" Or "H" And [Year]=2007,[SumCost]/.9,[SumCost])

The issue I am having is that it is increasing the costs for all types. Is
there a way to fix this or do i need a new query for C and H and then combine
the 2 queries? Thanks for any help you can provide.

 

[Klatuu]

Actually, you don't need to do it that way. Create a Where clause for the
query that returns only record of those two types:
WHERE [Type] IN("C","H") AND [Year] = 2007

Then just change your calculated field to:
TotalCost: SumCost]/.9

BTW, Type and Year are both Access reserved words and should not be used as
names. Wrapping them in brackets helps, but Access can get confused over
whether you mean a field named Year or the Year function. Type is an object
property name.

 

[John Spencer]

Your criteria argument is improperly structured. Try either of the
following

TotalCost:IIF([Type] in ("C", "H") And [Year]=2007,[SumCost]/.9,[SumCost])

OR

TotalCost:IIF([Type]="C" Or [Type] = "H" And
[Year]=2007,[SumCost]/.9,[SumCost])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..