Date calculations and saving them in a table

[Markg]

Hello All,

I have posted regarding issues on this project before but I think I am down to my final issue.

I am having problems with a calculation for a DVD rental system I am creating for my Friend.

I am currently creating the Rental form. When a customer returns a dvd I want to be able to search for their customer number (I plan to do this through generating and showing a query with date due back=0)

I will display a list of all customers with unreturned DVDs so that the user can check if the DVD is actually due. If it is,

I want the user to then be able to enter the relevant customerID into a text box and for the user to click a return DVD button.

On this button I want the duration to be calculated and saved in the Duration field- is the following correct?

Rentals.Duration= Datediff(“dd”,[rentals.Date Rented], [rentals.Date Due Back]

I also do not know where I should place the code, on the button when an update occurs?

Also I want the system to automatically work out the price for the rental including late fees. In this system I have chosen to have a standard rental duration of 3 days, charged at £1.50 a day and if the time is over that to charge each additional day at £2.50. I have come up with this code, again I am unsure if this would work;

Let N= rentals.Duration
Let P = rentals.Price

If N>= 4

Then

P= (3*1.50) + [(N-3)*2.50)]

Else

P= (N*1.5)

End if


I want this value to be displayed in a text box on the form and also saved in the rental table.


I have set up all other aspects of the system, Adding/Deleting records for DVDs and Customers etc.

I need help in finalising the actual code that I can put into Access 2003 and where exactly to put it.

I thank you in advance for your help

Mark

 

[Markg]

Hi Again,

I have created the form completely and have attached it. Hopefully this will help you understand what I am trying to acheive and show you the names of fields etc.