calculating dates

[lee123]

hi there it's been awhile since i have been on this forum but anyway how do you calculate dates in a query so that there are in currency for example:

if a person rents a room from 01/01/07 to 01/01/08 or 01/01/07 to 06/01/07
how would i get the total amount due if the rent was $540.00
in a query.

lee123

[puppydogbuddy]

hi there it's been awhile since i have been on this forum but anyway how do you calculate dates in a query so that there are in currency for example:

if a person rents a room from 01/01/07 to 01/01/08 or 01/01/07 to 06/01/07
how would i get the total amount due if the rent was $540.00
in a query.

lee123

lee,

You can try this:

Select DateIn, DateOut, DateDiff("d", [DateIn], [DateOut]) As RentDays, Rate, RentDays * Rate As AmountDue From YourTable

[zaidlig]

lee,

You can try this:

Select DateIn, DateOut, DateDiff("d", [DateIn], [DateOut]) As RentDays, Rate, RentDays * Rate As AmountDue From YourTable

That is correct if the rent is $540 per day. If your rent is based on months you'll want to change it to:

Select DateIn, DateOut, DateDiff("m", [DateIn], [DateOut]) As RentDays, Rate, RentDays * Rate As AmountDue From YourTable

Thats assuming you don't prorate months.

[lee123]

hi thanks for the reponse but im confused as to where i put this code in the query table?

lee123

[puppydogbuddy]

hi thanks for the reponse but im confused as to where i put this code in the query table?

lee123

First, you have to replace the non-aliased field names with the actual names used in your table. Then go to the SQL view of a query, paste the SQL, then run your query; modify as needed.