Fine calculation in a library management system project

[HowTo]

Hi all,

I am making a library management project and i am facing problems in fine calculation part. I have Date_Of_Issue and Date_Of_Submission fields (both set to Date/Time datatype) in a table named Book_Details(the database i am using is MS-Access 2003).

All i want to do is a query which will calculate the difference of days between the Date_Of_Submission and Date_Of_Issue.

If the difference of days is more than 5,then a fine will be charged by the student on a basis of $5 per day i.e; if the difference of days between the Date_Of_Submission and Date_Of_Issue is 7 days,then a fine of $10 will be charged ny the student.

How to do this?

Please please help

[si_the_geek]

This is the 'proper' way:

SELECT Date_Of_Submission, Date_Of_Issue, DateDiff('d', Date_Of_Submission, Date_Of_Issue) as NumDays
FROM

..but due to the way dates are stored internally, you should be able to use this version which is likely to be quicker:

SELECT Date_Of_Submission, Date_Of_Issue, Int(Date_Of_Submission - Date_Of_Issue) as NumDays
FROM

[HowTo]

Thanks for the quick reply

but i cant understand what you mean by the term proper way and much quicker way

[GaryMazzone]

Qicker.... Faster then the other. Proper the correct way to do this

[si_the_geek]

The proper way is the method shown in the documentation for Access (and Jet/ACE), and should be understandable by most people. It is also easy to change from Days to any other unit of time (such as Year/Month/Hour/miNute).

The quicker way uses a bit of advanced knowledge about Access/Jet (dates are stored as numbers, just like they are in VB6 [explanation of date storage etc]), which I expect takes less work, and thus runs faster. Unfortunately it is only valid for Days (or multiples of days, such as Weeks).