Importing files (97 SR2)

Topic

New Reply

Edited by HansV to prevent [Time] being converted to the 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([Time],2). All I get is #ERROR. The field came in as a number – long interger.
What could be the problem?

Thanks

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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 = 9×5+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

Reply | Quote