426,045 Members | 1,760 Online
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

# calculating dates

 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
4 Replies

 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

 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

 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