Database Function

[Guest]

I'm making a library database, and what I want to do is that for every day a student is late returning a book, then he is charged 20p . I have a field "Ideal date of Book Return", and "Date Book was Returned", and "Date Book was Borrowed", and "Amount Due" . These are the fields relevant to the function I want to produce. What function do I have to write, so that for each day a student is late to return a book after the three week default period that they have, they have to pay 20p?

 

[Sandra Daigle]

Take a look at the DateDiff Function - you can use it to get the number of
days between IdealDate and DateReturned - then multiply that number by the
current fine amount:

public Function GetFine(dtDue as Date, dtReturn as date, curFine as
currency) as currency
GetFine =datediff("d",dtDue,dtReturn) * curFine
end function

 

[Paul Falla]

Firstly, there is no need to have the "Amt Due" held in a
field in your table. It is better to create a query based
on the date fields in your table, and use this to
calculate the total due. Below is some SQL that you can
adapt to suit your needs. The syntax and logic will stay
he same, all you will need to do is change the table and
field names to coincide with the ones in your database.

SELECT TblLibrary.DateBorrowed,
TblLibrary.IdealDateOfReturn, TblLibrary.DateReturned, IIf
([TblLibrary]![DateReturned] Is Null,(Now()-[TblLibrary]!
[IdealDateOfReturn]),([TblLibrary]![DateReturned]-
[TblLibrary]![IdealDateOfReturn])) AS DaysLate, [DaysLate]
*0.2 AS AmtDue
FROM TblLibrary
WHERE (((IIf([TblLibrary]![DateReturned] Is Null,(Now()-
[TblLibrary]![IdealDateOfReturn]),([TblLibrary]!
[DateReturned]-[TblLibrary]![IdealDateOfReturn])))>=0));

Once you have got the query up and running, you can base
your form on it as opposed to basing it on the table.

Hope this helps

Paul

-----Original Message-----
I'm making a library database, and what I want to do is

that for every day a student is late returning a book,
then he is charged 20p . I have a field "Ideal date of
Book Return", and "Date Book was Returned", and "Date Book
was Borrowed", and "Amount Due" . These are the fields
relevant to the function I want to produce. What function
do I have to write, so that for each day a student is late
to return a book after the three week default period that
they have, they have to pay 20p?

 

[John Vinson]

I'm making a library database, and what I want to do is that for every day a student is late returning a book, then he is charged 20p . I have a field "Ideal date of Book Return", and "Date Book was Returned", and "Date Book was Borrowed", and "Amount Due" . These are the fields relevant to the function I want to produce. What function do I have to write, so that for each day a student is late to return a book after the three week default period that they have, they have to pay 20p?

The Amount Due should *not* be stored in your table at all, since its
value will change every day. Instead, you can set the Control Source
property of a textbox on a Form or Report to

=CCur(0.20 * DateDiff("d", [Ideal Date of Book Return], NZ([Date Book
was Returned], Date()))

The last bit with the NZ() will show the fine as of today if the book
is still outstanding.