Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    9

    Question Unanswered: Time datatype problem

    I have downloaded a XLS from one website in which duration of call against each telephone number is mentioned but it is mentioned in min for eg. if total min is an hour and thirty min (1 hr and 30) it is mentioned in a style of ninety min (One hour is equal to 60 min + 30 min).

    Now the problem starts when i want to paste this all in to Access file where it doesnt accept total duration of 96 min. therefore can you kindly advise how the datatype should be changed in the field in order to accommodate the information as i have downloaded.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'm confused by "where it doesnt accept total duration of 96 min." What datatype is the field you're trying to import it into? If you simply want to be able to see the time vs the phone number a text datatype will do. If you want to be able to do calculations with the minutes, a number datatype will surfice. The field would only have to be date/time dataype if you were actually importing, say, start time and end time for the calls.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2006
    Posts
    9

    Thanks but it doesnt work

    Actually the data what is imported from the website is like following for eg

    96:45 (96 min and 45 seconds)

    So if i use number data type then i cant perform calculations in the cell when data is 96:45 with 00:45 which gives the wrong answer if set the data type as number.

    Pls let me know.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    access doesn't have a time datatype.

    the appropriate datatype is either a datetime or a longinteger representing seconds. in either case, you will have to handle formatting manually since access datetime autoformatting will not display nn:ss more than 59:59 without switching to hh:nn:ss 01:00:01 (and similarly at the 24th hour).

    the access datetime is a 64 bit floating point number (like a 'double').
    numbers left of the decimal are days since (or before) some system-defined arbitrary day-zero which is not important for your situation.
    numbers right of the decimal are fractional days e.g. 6 hours = 0.25

    using your 96:45 as an example
    convert to seconds: (96*60) + 45 sec
    convert to fractionaldays: ((96*60) + 45)/86400

    given myInput as text in the form 'nnn:ss'

    dim myDateTime as date
    dim mySeconds as long

    mySeconds = ((clng(left$(myInput, instr(1, myInput, ":") -1)) * 60) + cint(right$(myInput, 2)))

    myDateTime = ((clng(left$(myInput, instr(1, myInput, ":") -1)) * 60) + cint(right$(myInput, 2)))/86400

    <untested, so might contain a typo or two>

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •