 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2006Elapsed 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> >1 - Im a new access user > 2 - I cannot download plug-ins or non-microsfot addons [quoted text clipped - 15 lines] > > 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 > "I have tried converting the excel field to text, number, and numerous > date/time formats to no avail." [quoted text clipped - 41 lines] > > > > db "... have Access convert it to a number ..." What kind of number? Integer, LongInt, Currency, Single, Double, ...? Jeff Boyce <Office/Access MVP> > 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 [quoted text clipped - 55 lines] >> > >> > 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> > 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 [quoted text clipped - 55 lines] >> > >> > 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.  SignatureJerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > 1 - Im a new access user > 2 - I cannot download plug-ins or non-microsfot addons [quoted text clipped - 13 lines] > > db |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.