If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Elapsed Time
"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 |
#3
|
|||
|
|||
Elapsed Time
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 |
#4
|
|||
|
|||
Elapsed Time
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 |
#5
|
|||
|
|||
Elapsed Time
"... 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 |
#6
|
|||
|
|||
Elapsed Time
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating elapsed time | andoh | Worksheet Functions | 5 | November 17th, 2005 11:31 AM |
Please Help! Ref Article ID 210504 | Telobamipada | New Users | 8 | May 9th, 2005 01:09 PM |
Previous Record Elapsed Time | Telobamipada | Running & Setting Up Queries | 0 | March 29th, 2005 04:43 PM |
Calendar Question | Josh | General Discussion | 7 | March 28th, 2005 11:19 PM |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |