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

DateDiff to calculate Weeks and Days function

P: 2
Hi everyone,

Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days between 2 dates amounts to 17 days. I want to now calculate in the query the amount of weeks and days within the 17 days to show 2 weeks 3 days.

I can create the function within excel, (A1 as cell with the days within it) as:

=INT(A1/7) & " Weeks, " & MOD(A1,7) & " days"

but cant seem to get the expression right in my Access query when I use INT and MOD.

I have found a very similar post from only a couple of weeks ago which is very close and helpful, but unfortunately my coding skills are not so good and so am having trouble dealing with the vb in the modules to create my own function.

http://www.thescripts.com/forum/thre...eeks+days.html

I seem to be so close, but a) am unsure of how I write this in VB and then b) what I then need to write within my Access Query to bring back the weeks and days.

Any help is very much appreciated.

many thanks,
Gerry
Aug 26 '07 #1
Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
For a calculated field in your query called WeeksDays this should do:

Expand|Select|Wrap|Line Numbers
  1. WeeksDays: (datediff("d",StartDate,EndDate)\7) & " Week(s) " & datediff("d",StartDate,EndDate) mod 7 & " Day(s)" 
You'll note the use of the \ instead of the / when dividing by 7. This returns only the integer part of the division.

10/3 = 3.33333333333333
10\3 = 3

Hope this helps!

Welcome to TheScripts!

Linq ;0)>
Aug 26 '07 #2

P: 2
The scripts is a modern marvel. Your answer has worked like a charm and my query now looks a work of art. Cant thank you enough for your time and help, it is really appreciated.

Cheers
Gerry
Aug 27 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Aug 27 '07 #4

Post your reply

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