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

Quote:

Originally Posted by 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

lee,

You can try this:

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

zaidlig

Quote:

Originally Posted by puppydogbuddy

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

Quote:

Originally Posted by lee123

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.

calculating dates

re: calculating dates

re: calculating dates

re: calculating dates

re: calculating dates

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about