If....... then statement in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing a query that combines 2 budget tables. The only problem that I
am having is the following:

table Budget Account Detail
fields Account Number
Account Period
Budget Amount

table Budget Account
field D_C Indicator

How do I say If D_C indicator ="C" then budget amount*-1 else
Budget amount.
 
Use the IIf format. In a query something like:

BAs: IIf([D_C indicator] ="C", [Budget Amount]-1 , [Budget Amount] )
 
Jerry
I tried that but when I go to run the query it a parameter box comes up for
d_c indicator and budget amount. Maybe I am not putting it in the right
place. I placed the formula in the criteria of the budget amount field. Is
this correct?

Jerry Whittle said:
Use the IIf format. In a query something like:

BAs: IIf([D_C indicator] ="C", [Budget Amount]-1 , [Budget Amount] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Freida said:
I am writing a query that combines 2 budget tables. The only problem that I
am having is the following:

table Budget Account Detail
fields Account Number
Account Period
Budget Amount

table Budget Account
field D_C Indicator

How do I say If D_C indicator ="C" then budget amount*-1 else
Budget amount.
Click to expand...
Click to expand...
 
Put the formula up in the Field row of the query, not the criteria. It could
be a new field so that you can see both the budget amount field and the new
calculated field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Freida said:
Jerry
I tried that but when I go to run the query it a parameter box comes up for
d_c indicator and budget amount. Maybe I am not putting it in the right
place. I placed the formula in the criteria of the budget amount field. Is
this correct?

Jerry Whittle said:
Use the IIf format. In a query something like:

BAs: IIf([D_C indicator] ="C", [Budget Amount]-1 , [Budget Amount] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Freida said:
I am writing a query that combines 2 budget tables. The only problem that I
am having is the following:

table Budget Account Detail
fields Account Number
Account Period
Budget Amount

table Budget Account
field D_C Indicator

How do I say If D_C indicator ="C" then budget amount*-1 else
Budget amount.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jerry it worked like a charm
Jerry Whittle said:
Put the formula up in the Field row of the query, not the criteria. It could
be a new field so that you can see both the budget amount field and the new
calculated field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Freida said:
Jerry
I tried that but when I go to run the query it a parameter box comes up for
d_c indicator and budget amount. Maybe I am not putting it in the right
place. I placed the formula in the criteria of the budget amount field. Is
this correct?

Jerry Whittle said:
Use the IIf format. In a query something like:

BAs: IIf([D_C indicator] ="C", [Budget Amount]-1 , [Budget Amount] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am writing a query that combines 2 budget tables. The only problem that I
am having is the following:

table Budget Account Detail
fields Account Number
Account Period
Budget Amount

table Budget Account
field D_C Indicator

How do I say If D_C indicator ="C" then budget amount*-1 else
Budget amount.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top