Trying to find a 0.00 in list of numbers currency (1 Viewer)

[turniporange23]

I want my textbox tbTotalAdditionalChargeAmount to show 0.00 if there is a total for that amount in tblAddition_ItMdt.AddictionChargeAmount

It will be a warning that field does not have a amount

strSQL = "SELECT SUM(AdditionChargeAmount) AS AdditionSum" & " FROM tblAddition_ItMdt WHERE HorseID=" & val(tbHorseID.value)

tbTotalAdditionalChargeAmount.value = Nz(recAddition.Fields("AdditionSum"), 0)

Thanks for any help.....................Bob

 

[theDBguy]

Maybe make sure there are no null values in the records?

 

[CJ_London]

Not really clear of the relationship between the two tables - is one supposed to be a total of the other - and how does 0.00 act as a warning? What if there are no values in the table?

You might want to investigate using the format property for the control? For numbers there are 4 states - positive,negative,zero and null, separated be a ;

So for example you might set it to

;;;[red]0.00

Or perhaps
;;;[red]”value required”

 

[turniporange23]

So when my code sums up say HorseID 216 and if it has a 0.00 amongst its totals , I want there to be a warning on my form so show that
Thanks for your help

 

[DHookom]

I don’t see any code that actually creates a recordset. I think you could use simple DSum() or DCount(). Warnings could be simple Conditional Formatting.

 

[turniporange23]

Maybe can I change this script to add only values less than 0.10
strSQL = "SELECT SUM(AdditionChargeAmount) AS AdditionSum" & " FROM tblAddition_ItMdt WHERE HorseID=" & val(tbHorseID.value)
Thanks for the help

 

[Ken Sheridan]

I cannot think of an occasion where I have allowed a column of Currency data type to be Null. Invariably I'd set the Required property of such a column to True (Yes), and its DefaultValue property to 0.

Null is semantically ambiguous. It is not a value, but the absence of a value, so has no meaning. About the nearest that can be given would be 'unknown', but that’s very much dependant on context. What would a Null in a CreditRating column mean for instance? You might say it means zero credit, but that's just a subjective interpretation. It could just as well be interpreted as unlimited credit.

Another issue with Null is that it propagates in arithmetical expressions. Any arithmetical expression of which a part is Null will evaluate to Null. regardless of other values in the expression.

In your case if you disallow Null and set the DefaultValue property to 0 then the value in a bound control will be zero if no other value is inserted at the column position. As others have pointed out, you can format the control or use conditional formatting so that zero stands out from non-zero values.

 

[projecttoday]

Null is undefined.