DateDiff to calculate Weeks and Days function

[Libber39]

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

[missinglinq]

For a calculated field in your query called WeeksDays this should do:

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)>

[Libber39]

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

[missinglinq]

Glad we could help!

Linq ;0)>