1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access: Elapsed Time Since Specified Date

Discussion in 'Business Applications' started by Gram123, Mar 19, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi.
    On a Access form, I'd like to have a control that identifies how long the database has been in operation.
    I'd like to be able to say that today is so many years, months & days since a specified date. Better yet, would be to include hours and minutes, or hours to 2 dps.

    At present I can only count days, using:
    =Date()-#13/02/99#+1 & " days"

    I know I can use the DateDiff function as an alternative, and then I can specify the interval, e.g. I could specify hours as the interval and it would return the number of hours that have passed since that date.
    But is there a way to specify multiple intervals?
    I want it to return:

    2 years, 1 month & 6 days

    but certainly not:

    2 years, 25 months & 761 days

    which I imagine it would, if I entered all arguments into a single text box, using yyyy,mm,dd.

    Someone on the Access Programmers site said that I should use DateDiff and then compute what I want from that result.

    Does this mean I need to do separate controls for each interval?
    And how would I 'compute' them?
    Gram
     
  2. Talismanic

    Talismanic

    Joined:
    May 25, 2000
    Messages:
    409
    I lifted this from a post on another board so don't ask me to explain it but this should give you the result you are looking for.

    Year: IIf(DatePart("m",[DOB]) = DatePart("m",Date()) And DatePart("d",[DOB]) > DatePart("d",Date()) Or DatePart("m",[DOB]) > DatePart("m",Date()), DateDiff("yyyy",[DOB],Date())-1, DateDiff("yyyy",[DOB],Date()))

    Months: IIf(DatePart("d",[DOB]) > DatePart("d",Date()), (DateDiff("m",[DOB],Date())-[Year]*12)-1, DateDiff("m",[DOB],Date())-[Year]*12)

    Days: DateDiff("d",DateAdd("m",[Months],(DateAdd("yyyy",[Year],[DOB]))),Date())
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Now that is just downright ugly code. Does it really do something or is this some cryptic message between you and Gram. I just know you're both ALIENS!!
     
  4. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Thanks for that Tal.
    Looks like I'll have to do it in three separate controls. I might give it a shot when I'm bored sometime, but for now I think I'll just stick with days...

    If I do it and it works, I'll post back with both my thumbs firmly up.
    Then Dreamboat can add "Solved" to the message and everyone's happy!

    Gram
     
  5. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I tried the year and month expressions in two separate controls and both threw up the #Name function.

    If I'm supposed to do these in VB code, then I've got no chance, so I'll just give up and accept the days function I already have in place.

    Thanks for your help anyway!

    Gram
     
  6. YSB

    YSB

    Joined:
    Mar 7, 1999
    Messages:
    779
    The code that Talismanic posted looks OK for the year part. I'll redo the Month and day parts though. In addition, I see no reason why you can't put it all in one text box. The only possible tough part here is storing and retrieving the date that the DB was created on. The easiest way is to hard-code it into your expression which is what I'll do for this example. If that won't work for you let me know and we'll have to do some fancier stuff.

    Let's assume that the date of DB creation was 2/3/1999. Your code would look something like this:

    "The DB was created " & IIf(DatePart("m", #2/3/1999#) = DatePart("m", Date) And DatePart("d", #2/3/1999#) > DatePart("d", Date) Or DatePart("m", #2/3/1999#) > DatePart("m", Date), DateDiff("yyyy", #2/3/1999#, Date) - 1, DateDiff("yyyy", #2/3/1999#, Date)) & " years, " & IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date), IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date)), DatePart("m", Date) - DatePart("m", #2/3/1999#)) - 1, IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date)), DatePart("m", Date) - DatePart("m", #2/3/1999#))) & " months, and " & IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date), DateDiff("d", DateAdd("m", -1, DatePart("m", Date) & "/3/" & DatePart("yyyy", Date)), Date), DatePart("d", Date) - DatePart("d",#2/3/1999#)) & " days ago"

    (Dreamboat, I guess that makes me the head alien? ;) )


    To explain this let's look at each part by itself. First the years part:

    IIf(DatePart("m", #2/3/1999#) = DatePart("m", Date) And DatePart("d", #2/3/1999#) > DatePart("d", Date) Or DatePart("m", #2/3/1999#) > DatePart("m", Date), DateDiff("yyyy", #2/3/1999#, Date) - 1, DateDiff("yyyy", #2/3/1999#, Date))

    The DateDiff function with the "yyyy" argument gives us the number of years between two dates. The problem is that DateDiff will return 1 if given the dates 12/31/2000 and 1/1/2001 even though they are only 1 day apart. That's because DateDiff ONLY looks at the year in this case and the year is 1 apart. To correct for this we have to subtract 1 if the current month is less than the month of the DB's creation because it is not yet a full year.

    IIf(DatePart("m", #2/3/1999#) = DatePart("m", Date) And DatePart("d", #2/3/1999#) > DatePart("d", Date)

    If the current month is equal to the DB's month and the current day is before the DB's day that would mean that the year is still not complete.

    Or DatePart("m", #2/3/1999#) > DatePart("m", Date)

    Or if the current month is less than the DB's month, in this case again the year is not yet complete.

    DateDiff("yyyy", #2/3/1999#, Date) - 1

    In either of these cases we subtract 1 from DateDiff. Otherwise, if the year IS complete, we take the result from DateDiff as-is.


    Next comes the months. The person who wrote Talismanic's code was relying that you could save the result of the Years calculation and use it in this one. In this case though that won't work. Therefore I used a different method.

    IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date), IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date)), DatePart("m", Date) - DatePart("m", #2/3/1999#)) - 1, IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date)), DatePart("m", Date) - DatePart("m", #2/3/1999#)))

    Again we need a different formula if the current month is lower than the DB's month. For example, 5/6/1999 and 4/6/2000 are exactly 11 months apart but 4/6/1999 and 5/6/2000 are 1 year and 1 month apart. In the case of 4/6/1999 and 5/6/2000 we merely need to subtract 4 from 5 to give us the difference (5-4=1) but in the case of 5/6/1999 and 4/6/2000 we need to subtract the difference from 12 to give us the correct number of months (12-(5-4)=11). On top of all this, we have to subtract 1 from the result if the month is not yet complete (if the current day is less than the DB's day).

    IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date)

    If the current day is less than the DB's day then the month is not yet complete and we must subtract 1 from the result of our calculation.

    IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date)), DatePart("m", Date) - DatePart("m", #2/3/1999#))

    Here is the meat of the calculation. If the current month is less than the DB's month then we have to subtract the difference of the two from twelve. Otherwise we just find the difference between the two.


    Last comes the days. The days pose an extra complication because you have to figure for months of different lengths. A month could be 28,29,30 or 31 days so simple math won't tell you how many days there are between 2/15/2000 and 3/9/2000. Therefore, once again, the DateDiff function comes in handy as it's already programmed with all leap years and which months have how many days.

    IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date), DateDiff("d", DateAdd("m", -1, DatePart("m", Date) & "/3/" & DatePart("yyyy", Date)), Date), DatePart("d", Date) - DatePart("d",#2/3/1999#))

    Let's break this down again:

    IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date)

    If the current day is less than the DB's day then we have an incomplete month. We have to count how many days there where from the last complete month to today.

    DateDiff("d", DateAdd("m", -1, DatePart("m", Date) & "/3/" & DatePart("yyyy", Date)), Date)

    Since the DB's date is 2/3/1999, the last complete month ended on the 3rd of the previous month. I use DateAdd to subtract one month from the third of the current month. That gives me the 3rd of the previous month. Then I use DateDiff to find how many days there where from then until today's date. Since DateDiff works according to the calendar, it will always give us the correct count.

    DatePart("d", Date) - DatePart("d",#2/3/1999#))

    If the current day is NOT less than the DB's day then we can simply subtract to find how many extra days there are.




    Now that I just spent several hours putting this together, I hope it actually helps someone! :)

    Let us know how it goes. Good Luck! :)
     
  7. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Thanks YSB, this is almost perfect.

    I found that where you have 'Date', Access interpereted that as a reference to a control called 'Date', so I changed it to 'Date()' and it worked.

    So it should be:

    "The DB was created " & IIf(DatePart("m", #2/3/1999#) = DatePart("m", Date()) And DatePart("d", #2/3/1999#) > DatePart("d", Date()) Or DatePart("m", #2/3/1999#) > DatePart("m", Date()), DateDiff("yyyy", #2/3/1999#, Date()) - 1, DateDiff("yyyy", #2/3/1999#, Date())) & " years, " & IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date()), IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date()), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date())), DatePart("m", Date()) - DatePart("m", #2/3/1999#)) - 1, IIf(DatePart("m", #2/3/1999#) > DatePart("m", Date()), 12 - (DatePart("m", #2/3/1999#) - DatePart("m", Date())), DatePart("m", Date()) - DatePart("m", #2/3/1999#))) & " months, and " & IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date()), DateDiff("d", DateAdd("m", -1, DatePart("m", Date()) & "/3/" & DatePart("yyyy", Date())), Date()), DatePart("d", Date()) - DatePart("d",#2/3/1999#)) & " days ago"

    This is great - complex, but great.
    The only thing I'd say about it is that if you have only 1 year, month or day showing, it still shows the plural - "1 years, 1 months and 1 days ago".
    I realise I'm just being awkward, and I don't really need to reslove this - it's brilliant as it is, and I don't want you to have to go through it all again for hours!

    Much appreciated,
    -Gram-
     
  8. YSB

    YSB

    Joined:
    Mar 7, 1999
    Messages:
    779
    I was afraid that might happen. I originally wrote the code with the () but when I tested it in the VB editor it automatically took them out. Since you are using this in a control instead of in VB you might still need them.

    As far as the pleural goes, the easiest solution is to just write it like this: 'Year(s), Month(s) and Day(s)'. It is possible to control whether or not the 's' appears but to do that in a control of this complexity is probably not worth the effort. If it's something you really need you would be better off writing a VB function where you have the option of storing values in variables.

    I'm glad I could be of help. Good Luck! :)


    [Edited by YSB on 03-23-2001 at 05:03 PM]
     
  9. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    You're right about putting a bracketted 's' after each interval. Makes sense.

    We'll call this one SOLVED then, shall we?

    Gram
     
  10. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Okay,

    Came back to work today and this control says "#Error".

    It turns out that it doesn't like dates that have less than 0 days, from the original (calculation) date.
    In this instance, the date I'm working from is 13/02/99 and I get this error message up when I change my PCs date to the 1st to the 12th of any month.
    So it accepts it if the days are 0 - i.e. it accepted 13/03/01 - but it doesn't accept 12/03/01 or indeed 02/04/01!

    Any ideas? YSB?

    Thanks in advance...

    Gram
     
  11. YSB

    YSB

    Joined:
    Mar 7, 1999
    Messages:
    779
    I just looked over the code you posted and I think I see where the problem is. Look again at this part of the code which calculates the days:

    IIf(DatePart("d", #2/3/1999#) > DatePart("d", Date), DateDiff("d", DateAdd("m", -1, DatePart("m", Date) & "/3/" & DatePart("yyyy", Date)), Date), DatePart("d", Date) - DatePart("d",#2/3/1999#))

    There are two things to keep in mind here. The first is that you seem to be using european dates with the day before the month while I'm using American dates with the month before the day. The second is that every time I wrote 'DatePart("d", #2/3/1999#)' I could have just written the number corrosponding to the days which would be 3 in American format. I wrote it the way I did to make it more clear but I could have written it like this:

    IIf(3 > DatePart("d", Date), DateDiff("d", DateAdd("m", -1, DatePart("m", Date) & "/3/" & DatePart("yyyy", Date)), Date), DatePart("d", Date) - 3)

    In one place I actually did that instead of writing out the whole DatePart(). The "/3/" actually represents the 3 from the days in American format. If you are using European date format the code for the days will have to look like this:

    IIf(DatePart("d", #13/02/1999#) > DatePart("d", Date()), DateDiff("d", DateAdd("m", -1, DatePart("d", #13/02/1999#) & "/" & DatePart("m", Date()) & "/" & DatePart("yyyy", Date())), Date()), DatePart("d", Date()) - DatePart("d",#13/02/1999#))

    In this case I reversed the month and day when creating the comparison date for DateDiff() to dd/mm/yyyy format instead of mm/dd/yyyy format. I also replaced the plain 3 with DatePart() so you can clearly see where to enter your date.

    Let us know how it works. Good Luck! :)

    [Edited by YSB on 04-02-2001 at 12:59 PM]
     
  12. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Okay,

    So I had taken the " /3/ " as literal characters, so I've amended these to the full date "13/02/99" so it is the same as all other instances of the date.

    Yes, you're quite right, I am using European date system (sorry, should have specified I'm in UK).
    As such, I guess I need to change round some of those 'd' and 'm' date parts, but I'm not really sure which...
    I've got lost in all that code and I might have inadvertently changed some of it round (yeah, I know).

    I don't want to end up with it thinking there's 365 months and 12 days in a year!

    This was never a vitally important thing, so I don't want you to have to spend ages sorting the whole thing into European dates - I can live without it!

    I'll try and go through it again and gert back to you if I solve it.

    (Confused) Gram
     
  13. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Okay, so I found what I'd messed up and corrected it and am now using the following:

    ="<Name> started work " & IIf(DatePart("m",#13/02/99#)=DatePart("m",Date()) And DatePart("d",#13/02/99#)>DatePart("d",Date()) Or DatePart("m",#13/02/99#)>DatePart("m",Date()),DateDiff("yyyy",#13/02/99#,Date())-1,DateDiff("yyyy",#13/02/99#,Date())) & " year(s), " & IIf(DatePart("d",#13/02/99#)>DatePart("d",Date()),IIf(DatePart("m",#13/02/99#)>DatePart("m",Date()),12-(DatePart("m",#13/02/99#)-DatePart("m",Date())),DatePart("m",Date())-DatePart("m",#13/02/99#))-1,IIf(DatePart("m",#13/02/99#)>DatePart("m",Date()),12-(DatePart("m",#13/02/99#)-DatePart("m",Date())),DatePart("m",Date())-DatePart("m",#13/02/99#))) & " month(s), and " & IIf(DatePart("d",#13/02/99#)>DatePart("d",Date()),DateDiff("d",DateAdd("m",-1,DatePart("d",#13/02/99#) & "/" & DatePart("m",Date()) & "/" & DatePart("yyyy",Date())),Date()),DatePart("d",Date())-DatePart("d",#13/02/99#)) & " day(s) ago"

    And it seemed to be working fine. So I thought I'd better test it. The only problem I can now see is with the months - Feb 1 to Feb 12 cause it to give minus one months, so when I tested 12/02/01, it gave me:

    "1 year(s), -1 month(s) and 30 day(s) ago"

    How do I fix the month to say 0?

    Cheers
    Gram
     
  14. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    YSB is four or five times the man I am. I never would have had the patience to work through all those iif functions, mainly because I'm particularly good at missing parentheses here and there, etc. And, like Dreamy, I am a fan of Cleanliness, it being next to Godliness and the like. Plus, I just like to fuss with things :) . And, of course, Gram, to help any fellow Access sufferer along in his suffering.

    There is another way to do what you're doing, which does not involve multiple controls, but rather a query which runs in the background, and just gives you back the answer.

    I did mine off a table called tblBusDays, which has a field called Dte, so that I could test a few thousand dates to make sure I wasn't going to lead you down/up the garden path. I also did it with a parameter called [Enter date to compare against].

    The resulting SQL statement looks like this:

    SELECT tblBusDays.Dte AS StartDate, [Enter date to compare against] AS ComparisonDate, [StartDate] & " falls " & [CorrectedYearDiff] & " year(s), " & [CorrectedMonthDiff] & " month(s), and " & [DaysDiff] & " day(s) before " & [ComparisonDate] AS Answer, DateDiff("yyyy",[StartDate],[ComparisonDate]) AS AccessYearDiff, DateAdd("yyyy",[AccessYearDiff],[StartDate]) AS ThisYearAccessDate, IIf([ThisYearAccessDate]>[ComparisonDate],[AccessYearDiff]-1,[AccessYearDiff]) AS CorrectedYearDiff, DateAdd("yyyy",[CorrectedYearDiff],[StartDate]) AS ThisYearDate, DateDiff("m",[ThisYearDate],[ComparisonDate]) AS AccessMonthDiff, DateAdd("m",[AccessMonthDiff],[ThisYearDate]) AS ThisMonthAccessDate, IIf([ThisMonthAccessDate]>[ComparisonDate],[AccessMonthDiff]-1,[AccessMonthDiff]) AS CorrectedMonthDiff, DateAdd("m",[CorrectedMonthDiff],[ThisYearDate]) AS ThisMonthDate, DateDiff("d",[ThisMonthDate],[ComparisonDate]) AS DaysDiff
    FROM tblBusDays;

    But all you're really looking for is one date to compare against today. So say you had a table called tblDBDetails, with a field called CreationDate, and the table had only one record, whatever date you wanted it to be. Then the SQL looks like this:

    SELECT tblDBDetails.CreationDate AS StartDate, Date() AS ComparisonDate, [StartDate] & " falls " & [CorrectedYearDiff] & " year(s), " & [CorrectedMonthDiff] & " month(s), and " & [DaysDiff] & " day(s) before " & [ComparisonDate] AS Answer, DateDiff("yyyy",[StartDate],[ComparisonDate]) AS AccessYearDiff, DateAdd("yyyy",[AccessYearDiff],[StartDate]) AS ThisYearAccessDate, IIf([ThisYearAccessDate]>[ComparisonDate],[AccessYearDiff]-1,[AccessYearDiff]) AS CorrectedYearDiff, DateAdd("yyyy",[CorrectedYearDiff],[StartDate]) AS ThisYearDate, DateDiff("m",[ThisYearDate],[ComparisonDate]) AS AccessMonthDiff, DateAdd("m",[AccessMonthDiff],[ThisYearDate]) AS ThisMonthAccessDate, IIf([ThisMonthAccessDate]>[ComparisonDate],[AccessMonthDiff]-1,[AccessMonthDiff]) AS CorrectedMonthDiff, DateAdd("m",[CorrectedMonthDiff],[ThisYearDate]) AS ThisMonthDate, DateDiff("d",[ThisMonthDate],[ComparisonDate]) AS DaysDiff
    FROM tblDBDetails;

    You can use whatever table, and whatever date field you want, just replace the two instances of the table name and the one instance of the date field name appropriately.

    Looks messy, I suppose, in SQL, but SQL always seems messy, and we can't paste Access query designs here yet! Cut & paste the SQL into a new query, open it in Design View, instead of SQL View, and it will look somewhat prettier.

    Say you save the query with some suitable name, such as qryReturnDateDiff. THEN on your form, for your ControlSource property, you'll have the following:

    =DLookup("Answer","qryReturnDateDiff")

    That's it. And by recopying the query, and changing the field and table details (and Date() if you want to test it against something other than today), you can use it against any date field. You may also want to play around with the text of the Answer field in the query.

    Hope this reply contributes to General Learning And Happiness as this thread has to mine ;) .

    [Edited by downwitchyobadself on 04-03-2001 at 06:36 PM]
     
  15. YSB

    YSB

    Joined:
    Mar 7, 1999
    Messages:
    779
    You're right. You have to add an = sign in one place. The part for Months should now look like this:

    IIf(DatePart("d", #13/02/1999#) > DatePart("d", Date()), IIf(DatePart("m", #13/02/1999#) >= DatePart("m", Date()), 12 - (DatePart("m", #13/02/1999#) - DatePart("m", Date())), DatePart("m", Date()) - DatePart("m", #13/02/1999#)) - 1, IIf(DatePart("m", #13/02/1999#) > DatePart("m", Date()), 12 - (DatePart("m", #13/02/1999#) - DatePart("m", Date())), DatePart("m", Date()) - DatePart("m", #13/02/1999#)))

    That should work. This was my error though. Nothing to do with the date format. (BTW it should actually say 11 months, not 0.) I don't think the date format should effect anything except the days part. The new code that I gave you for the days part in the last post should just simply replace the old code which it seems you already did. Let us know what happens. Good Luck! :)


    [Edited by YSB on 04-03-2001 at 07:04 PM]
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/40135

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice