Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing files (97 SR2)

    Edited by HansV to prevent <!t>[Time]<!/t> being converted to the <img src=/S/time.gif border=0 alt=time width=32 height=48> smiley

    I have imported some files from a cd. The files were .dat and I had to open the file in Notepad and save as a .txt. I imported them into access. I need to divide a time field using the Left(<!t>[Time],2). All I get is #ERROR. The field came in as a number - long interger.
    What could be the problem?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    Can you provide more info? How is the time stored in a Long Integer field?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing files (97 SR2)

    The time field is stored as a long integer. I have actually imported it in several formats (text, time, number) and I still can not get the left, right or mid function to work.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    Yes, you mentioned in your initial post that the time is stored as a long integer. What I'd like to know is how it is stored as a long integer. If you wish, you can attach a sample file.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing files (97 SR2)

    The database is massive. When you asked for HOW...what exactly do you mean?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    I (probably) don't need to see the database, but it would be nice to know the layout of the number field. For example, is 9:15 PM stored in the field as 915, or as 91500, or as 2115, or as 2115, or differently? At the moment, I have no idea what you're working with and what you're trying to do.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing files (97 SR2)

    It is stored as 093215. My goal is to separate as HH (09) MM (32) and SS (15). We have a program that captures when a user enters a systems and how long they spend in this system. Since we work shift, I need to display the time they entered so we can determine the shift. I would like to then concatenate and display the field as 09:32:15.
    I know this is difficult since you can not see my data. I have never encountered this issue before. I have always been able to manipulate the data coming form also any source.

    Thanks for your time and patience.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    If it's really stored as a long integer, the leading 0 is not really there, the number is actually stored as 93215. If it's a text field, the leading zero could be stored with the value. So I need to know: is it really a long integer, or is it a text field that looks like a long integer.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing files (97 SR2)

    There are no leading zeros in this import. I have imported previously as text and it contained the leading zeros. Either way will not work with the left () function. I get #error.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    Say that the imported long integer field is named LongTime. The following expression in a query should return the corresponding time:

    TimeSerial([LongTime] 10000, ([LongTime] 100) Mod 100, [LongTime] Mod 100)

    You can use this in the definition of a calculated column, or in the 'Update to' line of a date/time field in an update query.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing files (97 SR2)

    It worked like a charm. Can you explain to me why the other function (left, right, mid) would not work in this case? Also, can you explain to me what exactly did the formula do?

    One more, is there anything you don't know? I appreciate your help!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing files (97 SR2)

    The main problem is that the number has a variable length:
    12 AM (midnight) would be represented by 0 (one digit)
    11:22:33 AM would be represented by 112233 (six digits)
    This makes it more difficult to use the string functions Left, Mid etc.

    In the expression I used, is the integer division operator and Mod returns the remainder after division. For example:

    48 divided by 5 is 9, with remainder 3 (48 = 9x5+3), so 48 5 = 9 and 48 Mod 5 = 3.

    Let's analyze what happens with 91230:

    91230 10000 = 9. This is the number of hours.
    91230 Mod 10000 = 1230 (the remainder after dividing by 10000), and 1230 100 = 12. This is the number of minutes.
    91230 Mod 100 = 30 (the remainder after dividing by 100). This is the number of seconds.
    The TimeSerial function takes a number of hours, of minutes and of seconds and returns the corresponding time value.
    TimeSerial(9,12,30) returns 9:12:30

Posting Permissions

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