How do i calculate the difference between two dates and display the result in a field

[krellda]

I'm trying to make a simple database in access for work, i'm not brilliant with access, i know enough to make simple tables, forms and macros.

I'm trying to calculate the difference between two dates that i will be entering into two fields on a table.

Its a hire car company i need the difference between the date a car is hired out and the date it comes back in. I then want to multiply that number by the hire rate, say its £55 a day and the car is hired for 10 days.

I then need the result to be displayed in a field, possibly by using a button that says 'Calculate' or 'Hire cost'

eg. 01/04/2009 to 10/04/2009 inclusive multiplied by a hire rate of 55.

I have a table with the cars and thier rates and a table for this calculation (along with some other tables for clients etc.)

Is this possible? maybe using datediff? i've seen a few pages via google that say to use this but i can't work out how to do it. if anyone can help i'd really appreciate it.

thanks
Dan

 

[krellda]

OH and i'm on access 2003 and i do have excel but i'd like to avoid using it as the other guys here are really tech savy and i'm trying to make things as simple to use as possible.

 

[Scooterbug]

Couple of things. First, there is no need to store calculations. The best thing to do is to store the data in the calculation and do the actual calculation in a query. If you are just needing days, you can simply subtract the two dates. (Dates are actually stored as a number). So in a query, you could make the following field:

TotalDaysHired: ([ReturnDate]-[StartDate]*[HireRate])

Make sure that you replace the field names with actual field names from your table.

 

[krellda]

Couple of things. First, there is no need to store calculations. The best thing to do is to store the data in the calculation and do the actual calculation in a query. If you are just needing days, you can simply subtract the two dates. (Dates are actually stored as a number). So in a query, you could make the following field:

TotalDaysHired: ([ReturnDate]-[StartDate]*[HireRate])

Make sure that you replace the field names with actual field names from your table.

Thanks i'll give it a try

 

[DCrake]

TotalDaysHired: ([ReturnDate]-[StartDate]*[HireRate])

This will only work if both the start date and the hire date are populated, you need to take into consideration there being no return date. To get around this you need to amend it accordingly

TotalDaysHired: (Nz([ReturnDate],Date)-[StartDate])*[HireRate]

This way it will give you the current balance as of today

David

 

[krellda]

TotalDaysHired: ([ReturnDate]-[StartDate]*[HireRate])

This will only work if both the start date and the hire date are populated, you need to take into consideration there being no return date. To get around this you need to amend it accordingly

TotalDaysHired: (Nz([ReturnDate],Date)-[StartDate])*[HireRate]

This way it will give you the current balance as of today

David

the first one worked that suggested, after putting in your one the query is asking for the date, i don't want to have to enter it every time, if i have to i will but its there anything like datenow or todaysdate?

 

[DCrake]

TotalDaysHired: (Nz([ReturnDate],Date())-[StartDate])*[HireRate]

sorry type should have included the brackets

David

 

[krellda]

TotalDaysHired: (Nz([ReturnDate],Date())-[StartDate])*[HireRate]

sorry type should have included the brackets

David

Ok this is probably a silly question, its working but its only calculating 9 days instead of 10 on this test 1/04/2009 to 10/04/2009 it's technically right, but it needs to include the start and end date.

 

[krellda]

also how can i add VAT of 15%?
is there any way to display the nuber for only one form? i've added a save button as the form wasn't updating when i changed the numbers as far as the query was concerned.

 

[neileg]

Ok this is probably a silly question, its working but its only calculating 9 days instead of 10 on this test 1/04/2009 to 10/04/2009 it's technically right, but it needs to include the start and end date.

Add 1 to the result.

((Nz([ReturnDate],Date())-[StartDate])+1)*[HireRate]

 

[neileg]

also how can i add VAT of 15%?
is there any way to display the nuber for only one form? i've added a save button as the form wasn't updating when i changed the numbers as far as the query was concerned.

Multiply by 1.15, but remember it will be 1.175 from January.

Don't know enough about your form to advise. You should not be storing this value, you should calculate it in the form or in the query that your form is based on.

 

[DCrake]

Net: (Nz([ReturnDate],Date())-[StartDate])*[HireRate]
VAT(Nz([ReturnDate],Date())-[StartDate])*[HireRate]) * 0.15)

Then add both elements together

Gross:Net+Vat

 

[krellda]

Add 1 to the result.

((Nz([ReturnDate],Date())-[StartDate])+1)*[HireRate]

thanks thats working perfectly, just need a query to add the 15% it'd be best if i made a new field in the same query because we need both numbers for our records. so i'll do that. i tried noobishly to just write +15% but % causes a syntax error. I'm guessing i have to write is as a decimal?


Edit, oh thanks brillant forum.

 

[krellda]

Multiply by 1.15, but remember it will be 1.175 from January.

Don't know enough about your form to advise. You should not be storing this value, you should calculate it in the form or in the query that your form is based on.

sorry i was unclear about this, even tho the problem is now solved i think i should explain, the save button was for the form that i made for the cars we hire out, not for the calculation. when changing the dates on the form it wasn't updating the table unless i moved to the next form and back again. This meant that the query was calculating the previous numbers.

 

[krellda]

Is there an easy way to show only one result, ie i fill the info in press the query button and want only the result from that form as opposed to every result from every form that i've ever made, because otherwise the list will start stacking up and it would defeat the object of trying to simplify the previous system in place here.

Thanks
Dan

 

[Rich]

You should store the Vat rate at the point of sale and then calculate it instead of hard coding it in a query

 

[krellda]

You should store the Vat rate at the point of sale and then calculate it instead of hard coding it in a query

Oh! whats the difference, its still in the calculation. Surely it can't matter, can it?

And any idea about the query, is there a way to display just the one set of results. i've set it up so that when the button is pressed i get
Days hired
Rate of hire
Cost of hire
Rate of insurance
Admin cost
Sub total
Total (+vat)

but it get those results for every person even when i run the query on just one client. i'd rather have just the result for the client i'm dealing with.

Thanks
Dan

 

[DCrake]

How are you attempting to apply the filter to the query?

 

[krellda]

How are you attempting to apply the filter to the query?

i'm not sure, i'm a bit nooby on all of this really. i'm making this database to help me with this job more than anything, the previous system was too messy, even if the last guy knew how to use it, excel is not the best way to store logs like this

i'll try to explain the best i can

The fields i mentioned in my last post are all in the query. starting with a basic calculation then each one adds anoter little sum to get the final total with vat on. i am running the query via a form where i enter the start and end date of the hire and then the rate of hire, the query then calculates everything and displays a result.

when i add another form for the next client and press the same button i get the results plus the previous results from the first client.

Some clients are with us for longer periods of time than others so if i'm up to client 57 out of 157 who has his car back and is finished with his hire car, i'll have to find his result in a list of 157 other results and add it to his invoice.

if i could open the 57th result whilst on the 57th page that would be great

Thanks
Dan

 

[Rich]

The Vat rate is not a constant, storing the vat rate means you don't have to change codes accordingly
i.e. Total: [somefield]*[vatrate]