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

DateDiff interval use both month & week

P: 26
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.
Aug 10 '07 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,532
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)>
Aug 10 '07 #2

P: 26
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.
Aug 13 '07 #3

Expert 2.5K+
P: 3,532
There is no native Access function that will return the difference in months and weeks, so you have to build your own.

Expand|Select|Wrap|Line Numbers
  1. DaysDiff = DateDiff("d", [ifdob], [DateEntered])
  2. Months = Int(DaysDiff / 30)
  3. Days = DaysDiff Mod 30
  4. Weeks = Int(Days / 7)
  5. Days = Days Mod 7
  6. AdjustedWeeks = Weeks + (Round(Days / 7))
  8. 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)>
Aug 13 '07 #4

P: 26
Thanx so much. Im just starting to learn vb and this is a big help
Aug 13 '07 #5

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

Linq ;0)>
Aug 13 '07 #6

Post your reply

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