Elapsed Time

1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format and a
common entry would reflect say 40,000 hours. I have created a Link table to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db

"I have tried converting the excel field to text, number, and numerous
date/time formats to no avail."

What happens? No data appears, or error messages, or ...?

One approach would be to link to the Excel spreadsheet and use "parsing"
queries to load more permanent Access tables. You could use the queries to
do whatever conversion you needed to, and it sounds like you need the
"cumulative hours" field (in Access) to be at least a long integer (unless
you are referring to fractional parts of hours, too).

By the way, can you confirm that ONLY numeric values are in that cumulative
hours field in Excel? If you have any text or other data, it won't convert
to long integer.

BTW2, "40,000 hours" is not a date -- you cannot use a date/time field to
hold it.

BTW3, the "formats" you referred to describe how it will appear, not how it
will be stored in the table.

Good luck!

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format and
a
common entry would reflect say 40,000 hours. I have created a Link table
to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db

40000 hours wouldn't work as a date/time stamp in Access. Text would not give
you much flexibility in doing math. Sounds like a Double field might work
best; however, it won't handle the colon. Also double fields do strange
things like 3.11 - 3.1 = 9.99999999999979E-03. It just doesn't get floating
point math just right.

I'd be tempted to put the hours in one long integer field and minutes in
another long integer field. That would take splitting your data first.
Probably import it as a text field and use a combination of InStr, Left, Mid,
and Right functions to parse out the data.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"db" wrote:

1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format and a
common entry would reflect say 40,000 hours. I have created a Link table to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db

Couple things happen....if I convert it to a number in excel (non-date
format) I get a numeric overflow error. If I convert "as is", it deletes
some of the entries. If I change to text, import, then have Access convert
to a number...any entry over about 300 hours turns into #Num

"Jeff Boyce" wrote:

"I have tried converting the excel field to text, number, and numerous
date/time formats to no avail."

What happens? No data appears, or error messages, or ...?

One approach would be to link to the Excel spreadsheet and use "parsing"
queries to load more permanent Access tables. You could use the queries to
do whatever conversion you needed to, and it sounds like you need the
"cumulative hours" field (in Access) to be at least a long integer (unless
you are referring to fractional parts of hours, too).

By the way, can you confirm that ONLY numeric values are in that cumulative
hours field in Excel? If you have any text or other data, it won't convert
to long integer.

BTW2, "40,000 hours" is not a date -- you cannot use a date/time field to
hold it.

BTW3, the "formats" you referred to describe how it will appear, not how it
will be stored in the table.

Good luck!

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format and
a
common entry would reflect say 40,000 hours. I have created a Link table
to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db

"... have Access convert it to a number ..." What kind of number? Integer,
LongInt, Currency, Single, Double, ...?

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
Couple things happen....if I convert it to a number in excel (non-date
format) I get a numeric overflow error. If I convert "as is", it deletes
some of the entries. If I change to text, import, then have Access
convert
to a number...any entry over about 300 hours turns into #Num

"Jeff Boyce" wrote:

"I have tried converting the excel field to text, number, and numerous
date/time formats to no avail."

What happens? No data appears, or error messages, or ...?

One approach would be to link to the Excel spreadsheet and use "parsing"
queries to load more permanent Access tables. You could use the queries
to
do whatever conversion you needed to, and it sounds like you need the
"cumulative hours" field (in Access) to be at least a long integer
(unless
you are referring to fractional parts of hours, too).

By the way, can you confirm that ONLY numeric values are in that
cumulative
hours field in Excel? If you have any text or other data, it won't
convert
to long integer.

BTW2, "40,000 hours" is not a date -- you cannot use a date/time field to
hold it.

BTW3, the "formats" you referred to describe how it will appear, not how
it
will be stored in the table.

Good luck!

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format
and
a
common entry would reflect say 40,000 hours. I have created a Link
table
to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries
are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db

What is the underlying value stored in the Excel cell? What would you have
stored for your "40,000 hour" example?

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
Couple things happen....if I convert it to a number in excel (non-date
format) I get a numeric overflow error. If I convert "as is", it deletes
some of the entries. If I change to text, import, then have Access
convert
to a number...any entry over about 300 hours turns into #Num

"Jeff Boyce" wrote:

"I have tried converting the excel field to text, number, and numerous
date/time formats to no avail."

What happens? No data appears, or error messages, or ...?

One approach would be to link to the Excel spreadsheet and use "parsing"
queries to load more permanent Access tables. You could use the queries
to
do whatever conversion you needed to, and it sounds like you need the
"cumulative hours" field (in Access) to be at least a long integer
(unless
you are referring to fractional parts of hours, too).

By the way, can you confirm that ONLY numeric values are in that
cumulative
hours field in Excel? If you have any text or other data, it won't
convert
to long integer.

BTW2, "40,000 hours" is not a date -- you cannot use a date/time field to
hold it.

BTW3, the "formats" you referred to describe how it will appear, not how
it
will be stored in the table.

Good luck!

Jeff Boyce
Office/Access MVP

"db" wrote in message
...
1 - Im a new access user
2 - I cannot download plug-ins or non-microsfot addons

I am trying to compile a database from excel files which includes a
cumulative hours column. The field, in excel, is using [hh]:mm format
and
a
common entry would reflect say 40,000 hours. I have created a Link
table
to
the excel file.

I have tried converting the excel field to text, number, and numerous
date/time formats to no avail. On Link table update...several entries
are
deleted entirely.

Other posts I have found have not resolved this issue.

Thanks,

db