2007 Access if else statement that returns a text string

I have a access 2007 report for which one of the fields returns a numerical value of $0 if there no dollar amount associated with the project listed, else it returns a dollar amount greater than $0.

What I want to do is build the report so that if the field value = $0, I want it to read "TBD" (without the quotes) else, I want it to return the dollar amount.

Two part question--first, what Sub class would I write the code under?

And, how would I go about writin开发者_StackOverflow中文版g the if else statement?

Here is what I came up so far and it doesn't do anything (no errors, just returns the report with the $0's printed where no dollar amount is available).

Private Sub Cost_AfterUpdate()
If ([YearofExpenditureCost] >= 0) Then
Me.Cost = "TBD"
Else: Me.Cost = [YearofExpenditureCost]
End If

Where the [Cost] is the name of the text box containing the ControlSource value of [YearofExpenditureCost].

Many thanks in advance for any advice you can offer.

You can use an immediate If as the Control Source of a textbox on the report. Ensure that the testbox has a name other than any of the fields in the recordsource:

=IIF(YearofExpenditureCost=0, "TBD",YearofExpenditureCost)

The format property of the form has four parts (from Access help):

  First   The format for positive numbers. 
  Second  The format for negative numbers. 
  Third   The format for zero values. 
  Fourth  The format for Null values. 

Thus, if you set the format property to ;;"TBD"; it should display the value or "TDB" if it's zero -- there is no code required for this, just setting a property of the control on the report.