Day Difference in Table

[Captain Tycoon]

Hi,

I am in need of some help. What i am trying to do is to make a field in the table automatically generate data from a calculation. Below is how the table looks like:

- Date Due = this is a date
- Date Returned = the is a date
- Fine = this is where i need to put the calculation...

The calculation that im trying to do is:

The "Fine" field must always have £3 to begin with (default). It should add £0.25 for every day the game has been overdue. To do this, the field would need to calculate the Day difference between the "Date Returned" and "Date Due" fields. For example, if the day difference is 2 days between those 2 fields then in the "Fine" field, £0.50 should automatically be added.

I would love all the help i can get. Please remember that this is in the table.

Thanks,

[sierra7]

Hi

If you are working on a bound form then in the After_Update event of the ReturnDate you may need something like this.

 Dim lngPeriod as long 
 
'Calculate difference between two dates in days
'will return positive if first date is before second date
lngPeriod = DateDiff("d", [Date Due], [Date Returned])
If lngPeriod < 0 Then 
   'Negative, so Late, so calculate Fine
   Me!Fine = 3 -(0.25 * lngPeriod)
Else
   'Not late
   Me!Fine = 0
End If
 

S7

[MikeTheBike]

Hi,

I am in need of some help. What i am trying to do is to make a field in the table automatically generate data from a calculation. Below is how the table looks like:

- Date Due = this is a date
- Date Returned = the is a date
- Fine = this is where i need to put the calculation...

The calculation that im trying to do is:

The "Fine" field must always have £3 to begin with (default). It should add £0.25 for every day the game has been overdue. To do this, the field would need to calculate the Day difference between the "Date Returned" and "Date Due" fields. For example, if the day difference is 2 days between those 2 fields then in the "Fine" field, £0.50 should automatically be added.

I would love all the help i can get. Please remember that this is in the table.

Thanks,

Hi
You need to enter something like this in the query designer Field

Fine:IIF([Date Returned]>[Date Due],([Date Returned]-[Date Due])*0.25+3,0)

This assumes that both dates are dates and not date/time fields.

This has the fixed fine and day fine elements hard coded.
This is fine if these NEVER change, but if they do you may want to think about what you want to happen if/when they do !?

HTH


MTB

[missinglinq]

S7, I think from reading the original post that you got these two lines backward:

If lngPeriod < 0 Then

If the differemce, the game was returned early

fine = 3 - (0.25 * lngPeriod)

The 0.25 needs to be Added to the 3, not subtracted

After testing, it appears to need to be:

 Dim lngPeriod as long
 
'Calculate difference between two dates in days
lngPeriod = DateDiff("d", [Date Due], [Date Returned])
If lngPeriod > 0 Then
'Positive, so Late, so calculate Fine
Me!Fine = 3 + (0.25 * lngPeriod)
Else
'Not late
Me!Fine = 0
End If
 

There's something I think needs clarification, Captain! Does the 3 pounds always need to be in this field, even if the game is returned on time, or only if the game is returned late, with the daily fine added?

As you might have guessed from these answers, you cannot do this from a table, nor should you! Tables should never be accessed directly, but always thru forms/queries.

Welcome to TheScripts!

Linq ;0)>

[sierra7]

Hi Linq
What I got wrong was the dates the wrong way round and persuaded myself that if the deal was late then lngPeriod would be negative!
I knew it did not look 'intuitive' .
I must go to bed earlier!
S7

[missinglinq]

You're a programmer, and you actually sleep?

Linq ;0)>

[Captain Tycoon]

S7, I think from reading the original post that you got these two lines backward:

If lngPeriod < 0 Then

If the differemce, the game was returned early

fine = 3 - (0.25 * lngPeriod)

The 0.25 needs to be Added to the 3, not subtracted

After testing, it appears to need to be:

 Dim lngPeriod as long
 
'Calculate difference between two dates in days
lngPeriod = DateDiff("d", [Date Due], [Date Returned])
If lngPeriod > 0 Then
'Positive, so Late, so calculate Fine
Me!Fine = 3 + (0.25 * lngPeriod)
Else
'Not late
Me!Fine = 0
End If
 

There's something I think needs clarification, Captain! Does the 3 pounds always need to be in this field, even if the game is returned on time, or only if the game is returned late, with the daily fine added?

As you might have guessed from these answers, you cannot do this from a table, nor should you! Tables should never be accessed directly, but always thru forms/queries.

Welcome to TheScripts!

Linq ;0)>

Yes, It would need to always be £3 even if it is returned on time. I am currently trying to get this to work but im having a few problems since nothing seems to be happening...

What i did was make a form for the loans table, and added in the vb code you posted / fixed to the "Fine" field in the form. Please let me know if i have done anything wrong.

Thanks, and I appreciate all of the help Im getting from everyone.

[Scott Price]

One very important point to make is that calculated fields do not belong in tables!

You store data in tables, not calculations. Queries are for calculations.

You will be able to store historic data derived from a calculation in a table, but it is not good practice to store current fine data in the table for retrieval later. The reason for this: Say today you begin the overdue fine period and require your database to calculate and store today's fine. Tomorrow you need to do the same. Next day the same... etc etc. This is a very resource intensive way of structuring the fine system.

A better way: test for the overdue period and tell the database to turn the overdue item red, or popup a form letting you know of the situation. Then when the person returns the item, do your calculation in a query that returns once, and once only, the amount of the fine. You can do what you like with this fine amount then, store it in a table, etc...

Getting the idea?

Regards,
Scott

[missinglinq]

Say today you begin the overdue fine period and require your database to calculate and store today's fine. Tomorrow you need to do the same. Next day the same... etc etc. This is a very resource intensive way of structuring the fine system.

I'm confused by this, Scott. Nothing has been said about calculating the overdo fine each day; the fine is only calculated when the game is returned and the Date Returned is entered.

This code should do the job. The code goes in the AfterUpdate event for the Date Returned field. It's been adjusted so that the 3 pounds will appear even if the book is returned on time/early.

Private Sub Date_Returned_AfterUpdate()
Dim lngPeriod As Long
 
'Calculate difference between two dates in days
lngPeriod = DateDiff("d", Me.Due_Date, Me.Date_Returned)
  If lngPeriod > 0 Then
   'Positive, so Late, so calculate Fine
   Me.Fine = 3 + (0.25 * lngPeriod)
  Else
   'Not late
   Me.Fine = 3
  End If
End Sub

Scott's advice about storing the calculated amount is spot on; it shouldn't be stored but rther calculated whenever needed.

Linq ;0)>

[sierra7]

Yes, It would need to always be £3 even if it is returned on time. I am currently trying to get this to work but im having a few problems since nothing seems to be happening...

What i did was make a form for the loans table, and added in the vb code you posted / fixed to the "Fine" field in the form. Please let me know if i have done anything wrong.

Thanks, and I appreciate all of the help Im getting from everyone.

Ahoy Capt'n !
This is starting to make a nonesense of things.
You shouldn't call things by names they are not.

A Fine is what you get when after an infringement (like when you go past those little yellow cameras too fast in the UK) Linq just called me a Programmer and look how misleading that was!

It sound like you should have two fields on you database, one called StandardCharge or whatever and the other called Fine. As MikeTheBike has already said having values hard coded into your program is not a good idea. Right now you seem to be charging a flat-rate £3 for hiring a game. If you want to charge £3.25 next month or £4 for a different game or gendre, then your program won't work.

You could build thes costs into a separate look-up table and select the appropriate cost as you were issuing the game.

As Scott has said it is pointless calculating the 'Fine' and storing it in the database until the game is returned. If you wanted an on-screen display of the current fine you could include an unbound control and set its Control Source property to;-

 =IIf(Date>[Date Due],(Date-[Date Due])*0.25,0)
 

'Date' is a function that returns Today's date so doesn't need square brackets; [Date Due] is a field name so should have square brackets, but do yourself a favour and get rid of spaces in field names.

The above is similar to MikeTheBikes advice. If you see odd values (none multiples of 0.25) it will mean that [Due Date] may have a Time element to it. (dates are measured in whole days so 0.5 is 12.00 noon, 0.001 would be a 1/1000 of a day (1.44 minutes) after midnight) To avoid this you would have to use the DateDiff() function.

S7