Results 1 to 13 of 13
  1. #1
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8

    Calculating a time string not formatted as Date/Time

    Sorry if this comes across as unclear, it's sort of a fuzzy problem to begin with. And requires far to much explanation.

    I am getting information from a source which I have no control over. From the source there is a start time for an event, and a duration for the event. I need to combine the two to create an end time, or a stop time for the event. This wouldn't be so hard for me if it weren't for the ridiculous way in which the information is formatted at the source. There is a field for Date, Time, and Duration and they go like this:

    Date: Date is text displayed as 1110201, and would be February 1, 2011. 1100903, would be September 3 2010. The first 1 = the first two numbers of the year, in this case 20, the second two numbers equal the last two numbers 10, the middle two are the month and the las two are the day 110 = 2010, 111 = 2011, 02 = February and 01 = the first. I need this to display in short date, 2/1/2011.

    Time: Time has no colons. 11:35:22 comes across as a text field. The time is military. So 233521 is 11:35:21 PM (HH:MM:SS), I need the query to display it as that but that leads me to the second problem.

    Duration: Is just a single number in MINUTES. Durations can be for an entire date and would display as 1440, or multiple days, or 1560. This would be 1,560 Minutes. No seconds, no hours, just minutes.



    So the problem is I need to get ALL of this information to come out of a query, not display in the manner it's displayed in and chomp it up in excel, which I could do for myself, but it's not for myself and the people utilizing it need it to come out as Date Start Time Stop Time. Where the stop time is calculated between the Duration and Start Time...

    Could anybody give me any assistance? Or am I completely out of luck here, heh.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Will you be using these as criteria in queries or do you want it formatted for display purposes? If the former, It's gonna take some fun VBA. if the latter, not too hard.

  3. #3
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    Oh I think we all know the answer to that! The events are setup linked to a SQL farm that they can only access via Access. The queries are for other people not for myself. I usually crunch the information in Analyzer and put the reports elsewhere for viewing Crystal/PDFs.

    But ya, criteria for queries....several queries. A single person's full day of events, search for every event that goes over midnight, search for all events whose end time is past 5:00 PM, Show all people and event type that is longer than 24 hours...so on and so forth. It's a whole bunch of queries that are going to be run against these things by a whole group of people.

    The linked table is updated in real time, and this is for real time coaching. It would be so great, or so easy if it were for a report I could just put out in Excel for the previous day, but it's just not the case.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Actually, I take back the VBA part. This could be done a bit easier. the first three numbers in your date, 111, would be 2011, 110, 2010. How are years in the 19xx's displayed? Are they even needed?

  5. #5
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    There aren't any. This specific datasource does not have anything before 2009, 109.

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Here's the date part. I will update this post with the rest.
    date:
    CDate(mid([fieldName],4,2) & "/" & right([fieldName],2) & "/20" & mid([fieldName],2,2))

    time:
    CDate(left([fieldName],2) & ":" & mid([fieldName],3,2) & ":" & mid([fieldName],5,2))

    duration:
    concatenate [startDate] and [startTime] to form a "long date" datatype. Once you do, update your [endTime] with
    DateAdd("n",[duration], [concatenatedFieldName])

  7. #7
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    Time + Duration = End Time

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The first two i tested and can confirm the syntax is good. The third is more of a logical thing you can work out the syntax to.

    Moral of the story, the major text-to-date functions are CDate() and Format(). The addition to or subtraction from date functions are DateAdd(), DatePart(), DateDiff(), etc

    More info here. Scroll down to the date function section for more info.

  9. #9
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    Thank you. The help is much appreciated. Both the first two work magnificently

  10. #10
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Anytime. Once you get the third one working, go ahead and mark the thread as solved (from the "thread tools" menu) so others can search through the forums for it.

  11. #11
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    Will do, I havent had time to try and concatenate the date and time to make a date/time longdate field yet. If my duration is in minutes should I multiply it by 60 and then do the DateAdd?

  12. #12
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    No. the "n" in DateAdd tells the function that the number being added is in minutes. It should be good as-is.

  13. #13
    Jasrenkai is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    8
    Petty cool. Work liked a charm, easier than I had anticipated. Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-04-2011, 05:43 PM
  2. Calculating Trip Time
    By CSUjr in forum Access
    Replies: 2
    Last Post: 08-06-2010, 05:06 AM
  3. Replies: 2
    Last Post: 07-03-2010, 08:45 PM
  4. Split string into Date and Time Columns?
    By Hobbes29 in forum Queries
    Replies: 2
    Last Post: 06-08-2010, 06:50 PM
  5. Need help with calculating Time total
    By Monoceros in forum Reports
    Replies: 1
    Last Post: 04-20-2009, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums