DateDiff interval use both month & week

[BUmed]

I want to set up a Query to show the diffence between 2 dates and the output to be in months and weeks. So the child is 15months and 2weeks (15/2). I have been try to modify DateDiff Age: DateDiff("m/ww",[ifdob],[DateEntered]) but cant get it to work. Any suggestions.
thanx
Steve

[missinglinq]

Obviously, when you do these kinds of calculations, you have to make some compromises, i.e what are you going to consider to be a month, 30 days or 30.41666 days and so on. Then you have to decide what you want to do with the leftover days. The difference between two dates is seldom going to be exactly X Months and Y Weeks.

Using 30 days to a month and being satisfied with the whole months and weeks (discarding the part of a week) is fairly simple. So would getting Months/Weeks/Days. And this doesn't have to be done with a query; you can simply do a calculation in your form.

Think about this and post back.

Welcome to TheScripts!

Linq ;0)>

[BUmed]

I was just wondering if datediff could be used to output a combination of intervals. But you are saying that I would have to calculate it using another method if it is to be month and weeks. For example if a child is 13m, 2week and 3days it would be ok to round off the days to simple display 13m and 2weeks. Can you suggest the best way of doing this.
thanx

[missinglinq]

There is no native Access function that will return the difference in months and weeks, so you have to build your own.

DaysDiff = DateDiff("d", [ifdob], [DateEntered])
Months = Int(DaysDiff / 30)
Days = DaysDiff Mod 30
Weeks = Int(Days / 7)
Days = Days Mod 7
AdjustedWeeks = Weeks + (Round(Days / 7))
 
DateDiff Age = Months & " Month(s) and " & AdjustedWeeks & " Week(s)"
 

Line # 1 uses DateDiff to determine the difference between the two dates in days.

You'll remember from grade school that when you divide a number you get a whole number and a remainder. In Access, to get only the whole number, you format your answer by using the Int (stands for Integer) function, so Line # 2 gets the number of whole months by using Int(Days/30)

In Access, to get just the remainder from a division operation, you use a function called MOD. 10 / 3 = 3 with 1 remaining, so 10 MOD 3 = 1. Line # 3 uses this to find the days remaining after the months have been calculated.

Lines # 4 and 5 repeats this same operation, using 7 this time to calculate the number of weeks and days left in the remaining number of days.

The final line puts these concatenates these segments to give you your final answer.

Linq ;0)>

[BUmed]

Thanx so much. Im just starting to learn vb and this is a big help

[missinglinq]

Glad we could help.

Linq ;0)>