By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

calculating dates

lee123
100+
P: 556
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
Jan 12 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Jan 12 '08 #2

P: 45
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.
Jan 12 '08 #3

lee123
100+
P: 556
hi thanks for the reponse but im confused as to where i put this code in the query table?

lee123
Jan 12 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 12 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.