Conditional statements in text box properties

[jGURU]

I am trying to have the results of a calculation automatically stored/displayed in a textbox named Overdue.text. Basically i only want positive whole values as fines are based on the number of days people are overdue. DateReturned.text and DateDue.text are Date/Time fields.

If DateReturned.Text > DateDue.Text Then
Overdue.Text = DateReturned.Text - DateDue.Text
Else
Overdue.Text = 0
End If

I tried the above and it doesn't work for me. Is it because Overdue.text is set as Integer and not Date/Time? Any alternative ideas?

Any help would be most appreciated.

 

[Mile-O]

I'm guessing you have actually called your textboxes Overdue, DateReturned, and DateDue and are referencing their text property rather than actually calling them Overdue.Text, DateReturned.Text, and DateDue.Text.

If you have called them by the latter then change them now - txtOverdue, txtDateReturned, and txtDateDue would be more acceptable titles for your textboxes.

As I said though, I believe you are referencing the text property of the textbox which would mean your dates are being treated as text values rather than dates.

If you just reference the textbox VBA will default to the textbox's Value property and you should have no problems with the code. I've added the Me keyword to the code just to make it that little more complete. I've also condensed it into one line using the IIf() function.

Me.Overdue = IIf(Me.DateReturned > Me.DateDue, DateReturned - DateDue, 0)

 

[jGURU]

Thanks Mile for your prompt reply. One more question...how do i round off the number to a whole number? I have used integer in the Overdue field but it still doesn't work.

Many thanks.

 

[Mile-O]

If you are subtracting one date from another how are you getting a non integer value?

Are your date fields using times also?

Try this for just now, though:

Me.Overdue = IIf(Me.DateReturned > Me.DateDue, Int(DateReturned - DateDue), 0)

 

[dcx693]

There is a Round function in Access 2000 and higher.

 

[jGURU]

Thanks Mile and dcx - worked like a charm. When performing calculations, do i have to use the old BODMAS (Brackets Of Division Multiplication Addition Subraction) routine in that order, or does anything go?

What i'm trying to do is this:

Overdue=[AmountPaid]-([Balance]+Forms!frmFilmReturn!Fine)

...so that i can add any outstanding or previous balance to the fine and then subract it from the amount.

All i get is #Error or #Name in the textbox. Any suggestions would be appreciated.