Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Expression in query (2000 and XP)

    I'm building a receipt (i.e., a report) to give to our winning bidders at a fund-raiser auction. The receipt will list the item(s) the bidder purchased, the fair market value of the item(s), and the amount the bidder paid. We also want to give the bidder the amount he or she can deduct on their income tax return. That amount will be, of course, the difference between the fair market value and the amount they paid IF the amount they paid is higher than the fair market value.

    I tried putting the following expression into an unbound text box: =([BidWinner]-[ItemStatedValue]). Well, that approach works, but I want to display a value only if that value is positive (greater than zero). All values will be in whole dollars, so I don't need a decimal. I'm thinking maybe I should add the expression to the source query. I tried that, and now the query (in Expr1) shows the negative values in parentheses.

    Where do I go from here?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression in query (2000 and XP)

    Putting the expression as a calculated field in the source query is more efficient, but putting it in the control source of a text box on the report will work as well. Use the Format property to weed out negative values. The Format property can consist of up to 4 sections: the first for positive numbers, the second for negative numbers, the third for zero values and the fourth for null (empty) values.

    If you want negative values to be displayed as 0, use 0;0
    If you want negative values to be hidden, use 0;""
    If you want negative and zero values to be hidden, use 0;"";""

    Note: it would also be possible to use the IIf function to modify negative results.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Expression in query (2000 and XP)

    Thank you, Hans--that's almost exactly what I was looking for, and the solution was simpler than I'd expected!

    One more question: This field should look just like its neighbors, i.e., it should look like a currency field. I can set the format for this field to Currency, but what shall I do then about making the field invisible? (We decided to populate the box only when there's a positive value to display.)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression in query (2000 and XP)

    Set the format to (for example):<pre>$ 0;"";""</pre>

    or, if you want the dollar sign to be left-aligned and the value right-aligned:<pre>$* 0;"";""</pre>


  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Expression in query (2000 and XP)

    Thank you, thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •