Error trapping calculations Float/Currency

[1HuntnMan]

Anyone experienced with error trapping adding/subtracting float values defined AsCurrency?

See below a short procedure which is my best try but I'm sure someone is more experienced that I with designing accounting type programming...

Code: Pascal  [Select][+][-]

1. procedure TFrmInvoices.JDBCurrencyAmtDueClick(Sender: TObject);
2. begin
3.   if DbfInvces.FieldByName('PAIDAMT').AsFloat = Null then
4.     DbfInvces.FieldByName('AMTDUE').AsFloat:=
5.      DbfInvces.FieldByName('INVCEAMT').AsFloat-
6.      DbfInvces.FieldByName('DISCOUNT').AsFloat
7.   else
8.     DbfInvces.FieldByName('AMTDUE').AsFloat:=
9.      DbfInvces.FieldByName('INVCEAMT').AsFloat-
10.      DbfInvces.FieldByName('DISCOUNT').AsFloat;
11.   { This is locking with 'not a valid number'
12.   DbfInvces.FieldByName('AMTDUE').AsFloat:=
13.    DbfInvces.FieldByName('INVCEAMT').AsFloat-
14.    DbfInvces.FieldByName('DISCOUNT').AsFloat-
15.    DbfInvces.FieldByName('PAIDAMT').AsFloat; }
16. end;  
17.  

try ... except
I'm trying this...

[alpine]

Are you sure all fields involved are of the appropriate type/class?
Such kind of message is given from the StrToFloat called from TStringField.GetAsFloat.

[wp]

Did not check it, but I would expect to get a similar error message when one of the operand fields of the calculation is NULL.

[paweld]

Code: Pascal  [Select][+][-]

1. procedure TFrmInvoices.JDBCurrencyAmtDueClick(Sender: TObject);
2. begin
3.   if DbfInvces.FieldByName('PAIDAMT').IsNull then
4.     DbfInvces.FieldByName('AMTDUE').AsFloat:=
5.      DbfInvces.FieldByName('INVCEAMT').AsFloat-
6.      DbfInvces.FieldByName('DISCOUNT').AsFloat
7.   else
8.     DbfInvces.FieldByName('AMTDUE').AsFloat:=
9.      DbfInvces.FieldByName('INVCEAMT').AsFloat-
10.      DbfInvces.FieldByName('DISCOUNT').AsFloat-
11.      DbfInvces.FieldByName('PAIDAMT').AsFloat;
12. end;

Or simpler:

Code: Pascal  [Select][+][-]

1. procedure TFrmInvoices.JDBCurrencyAmtDueClick(Sender: TObject);
2. var
3.   c: Currency;
4. begin
5.   c := DbfInvces.FieldByName('INVCEAMT').AsFloat -
6.     DbfInvces.FieldByName('DISCOUNT').AsFloat;
7.   if not DbfInvces.FieldByName('PAIDAMT').IsNull then
8.     c := c - DbfInvces.FieldByName('PAIDAMT').AsFloat;  
9.   DbfInvces.FieldByName('AMTDUE').AsFloat := c:
10. end;

[1HuntnMan]

All I'm trying to avoid if the user doesn't enter anything in the JDBCurrencyPrice component. Here's my latest try:

Code: Pascal  [Select][+][-]

1. procedure TFrmPhotogrphyMgt.JDBCurrencyPriceExit(Sender: TObject);
2. begin
3.   if JDBCurrencyPrice.AllowNull = False then
4.     if MessageDlg ('Price can not be left null, continue?', mtConfirmation,
5.    [mbYes, mbNo],0) = mrYes
6.   then
7.     JDBCurrencyPrice.SetFocus;
8. end;
9.  

The behavior is if you enter in other fields and get to the Price and try to tab past it you get the attached error, them you get the MessageDlg above.  If you click 'Yes' then you go back and attempt to enter let's say 125.50. Then it get's in a loop with my message and the Stupid Error.

There's got to be a way to do this without the Stupid Error! It seems to me anyone experienced in programming accounting systems has delt with this or maybe not even using a Float field and defining it as Currency this way:

Code: Pascal  [Select][+][-]

1.   TFloatField(DbfPhotos.FieldByName('PRICE')).Currency:= True;
2.  

Okay, other posts are very helpful while I was typing this reply, thanks all!

[Thaddy]

One remark: the currency type is technically not a float but a scaled from integer type, a.k.a. a fixed point type with a maximum error of  0.00005. You should be very careful with calculations.
Because if you use real/single/double calculations on a currency type you may introduce rounding errors that are otherwise avoided by convention: banking software therfor avoids this mix-up like the plague and only use the currency type without any - other - floating point operations. It should only use shifts by 5 (6) and integer math. It is usually OK to assign to a single, but not for account/ledger operations: these should be done in pure currency only.

[alpine]

Did not check it, but I would expect to get a similar error message when one of the operand fields of the calculation is NULL.

I have checked it, AsFloat will return 0.0 in the case of NULL. That error is from String->Float conversion (of an empty string) and probably is generated somewhere into the db aware controls used.

@1HuntnMan
The event-driven nature of the data aware controls is a source of numerous loops and locks, can you limit your checks just at the dataset level, on BeforePost perhaps?

[1HuntnMan]

Okay, so, what I'm learning is that these Currency type fields or rather at the table level I'm defining them as Float 7,2. But in the three Units i.e. Proposals, Sales Orders and Invoices, I'm attempting to  error trap to avoid the issue if the User tabs or clicks to for example the Price. In the Form-Show Procedure of these Units I'm defining the Float fields AsCurrency and using the JujiboDB component TJBDBCurrencyEdit.  This component evidently has a build in Error trap if the user tabs to one of the Float AsCurrency fields and they skip any entry by tabbing to the next Edit Field or after entering the Currency Edit, you get the attached error before I can try to catch it, like so...

Code: Pascal  [Select][+][-]

1. procedure TFrmPhotogrphyMgt.JDBCurrencyPriceExit(Sender: TObject);
2. var
3.   c: Currency;
4. begin
5.   c:= DbfPhotos.FieldByName('PRICE').AsCurrency;
6.   if c = Null then
7.     if MessageDlg ('Price can not be left null, continue?', mtConfirmation,
8.    [mbYes, mbNo],0) = mrYes
9.   then
10.     JDBCurrencyPrice.SetFocus;
11. end;
12.  

Testing I get the JDBCurrencyPrice edit error and then my error Procedure above pops up, see code above. I'm wondering if I should just use a DBEdit component and define the EditMask and CustomEditMask=True? The attached error pop-up I believe is coming from the JDBCurrencyEdit component before it get's to my MessageDlg pop-up.

[five]

It should work with this modification.

Code: Pascal  [Select][+][-]

1.  
2. procedure TFrmPhotogrphyMgt.JDBCurrencyPriceExit(Sender: TObject);
3. begin
4.   if  DbfPhotos.FieldByName('PRICE').IsNull then
5.     if MessageDlg ('Price can not be left null, continue?', mtConfirmation,
6.    [mbYes, mbNo],0) = mrYes
7.   then
8.     JDBCurrencyPrice.SetFocus;
9. end;
10.  
11.  

[1HuntnMan]

Thanks, I'll try that and let you know...

[1HuntnMan]

Nope, doesn't work. I went to an exist record and tabbed down to the Price field. Deleted the contents and tabbed to the next field and JDBCurrencyPriceExit isn't even getting execute per the OnExit even. An internal error message is popping up, see my previous Post.

I'm thinking and think I'll change to check for Null via the OnChange event instead of the OnExit event and see if that catches it before the JDBCurrencyEdit pops up it's own error msg...

[five]

I think your are not using the proper event to do some validation. A suggestion, you can do a function to validate the returned value for float fields :

Code: Pascal  [Select][+][-]

1.  
2. Function ValidateCurrency (Field : TField) : Float;
3. begin
4.    if Field.IsNull then
5.      Result : 0.0
6.    else
7.      Result := Field.AsFloat;
8. end;  
9.  
10.  
11. // In your JDBCurrencyAmtDueClick click event
12.  
13. procedure TFrmInvoices.JDBCurrencyAmtDueClick(Sender: TObject);
14. begin
15.  
16.      DbfInvces.FieldByName('AMTDUE').AsFloat:=
17.       ValidateCurrency(DbfInvces.FieldByName('INVCEAMT'))-
18.       ValidateCurrency(DbfInvces.FieldByName('DISCOUNT'))-
19.       ValidateCurrency(DbfInvces.FieldByName('PAIDAMT'));
20. end;
21.  
22.  
23.  

You can also use the Field.OnValidate event related to each field

[wp]

Okay, so, what I'm learning is that these Currency type fields or rather at the table level I'm defining them as Float 7,2. But in the three Units i.e. Proposals, Sales Orders and Invoices, I'm

Code: Pascal  [Select][+][-]

1. procedure TFrmPhotogrphyMgt.JDBCurrencyPriceExit(Sender: TObject);
2.  

In my opinion it is not a good idea to check for errors in the OnExit event of controls because you very easily run into unforeseen and unrecoverable error situations. Suppose your controls are in a dialog form with OK and Cancel buttons. As usual, OK will accept the control values and handle them further (e.g. post them into a database), and Cancel will ignore all changes (even if controls contain erroneous values) and just close the dialog without doing anything else. In this situation with error handling in the OnExit events it is not possible to press the Cancel button, because when you do this the OnExit of some control will fire and trigger the validation procedure, and when there is an error in this control you will see the error message, but you wanted to ignore the input due to the Cancel pressed.

you have much more flexibility when you check errors only when something should be done with the values. For example, when the OK button is clicked, or imediately before dataset is posted (OnBeforePost event) - if there is some error somewhere in the latter case raise a silent Abort exception to inhibit further processing.

[1HuntnMan]

I tried this...

Code: Pascal  [Select][+][-]

1. procedure TFrmPhotogrphyMgt.DbfPhotosBeforePost(DataSet: TDataSet);
2. var
3.   c: Currency;
4. begin
5.   c:= DbfPhotos.FieldByName('PRICE').AsCurrency;
6.   if c = Null then
7.     if MessageDlg ('Price can not be left null, continue?', mtConfirmation,
8.    [mbYes, mbNo],0) = mrYes
9.   then
10.     DBEditPrice.SetFocus;
11. end;
12.  

This doesn't execute or Price has something other than NULL but changed to 2 records by deleting the contents of the PRICE field so that they were empty. Saved and the above didn't execute.
I exited my test app and checked the table's contents with Dbf Table Manager and both records had empty values in the Price field.  Hmmm ...

[alpine]

You can't compare Currency variable to Null since Null function is provided for Variant variables. See five's post #8.
Your comparison will always be False.