Elapsed Time

D

db

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
 
J

Jeff Boyce

"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>
 
J

Jerry Whittle

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.
 
D

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 said:
"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 said:
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
Click to expand...
Click to expand...
 
J

Jeff Boyce

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

Jeff Boyce
<Office/Access MVP>

db said:
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 said:
"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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

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 said:
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 said:
"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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top