Retain Field Format From Excel

I have an excel file that I'm importing to Access.  This field consists of 
"time"...hours, minutes, seconds.  The format in excel is set to "Custom" - 
[h]:mm:ss.

The data elements look like 150:27:50, 2:30:25, etc...

The time stays the same for some records but others change. For 
example...150:27:50 becomes 6:27:50 AM when imported.

If someone can help me figure a way to retain the format for all records I 
would greatly appreciate it. 

Thank You,
Lisa W. 


0
Utf
2/10/2010 5:31:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
738 Views

Similar Articles

[PageSpeed] 28

150 divided by 24 = 6.25 or 6 and 1/4 days which is displayed a 6 AM.
You need to use a text field for datatype to keep the format but you can not 
do math on it without massaging.

-- 
Build a little, test a little.


"Lisa W." wrote:

> I have an excel file that I'm importing to Access.  This field consists of 
> "time"...hours, minutes, seconds.  The format in excel is set to "Custom" - 
> [h]:mm:ss.
> 
> The data elements look like 150:27:50, 2:30:25, etc...
> 
> The time stays the same for some records but others change. For 
> example...150:27:50 becomes 6:27:50 AM when imported.
> 
> If someone can help me figure a way to retain the format for all records I 
> would greatly appreciate it. 
> 
> Thank You,
> Lisa W. 
> 
> 
0
Utf
2/10/2010 6:21:02 PM
The date/time data type in Access represents a point in time, not a time
duration , so, as Karl has pointed out, you could to import the value as a
text data type.  If you do need to do time arithmetic on the values you can
convert the value to seconds with a function like this:

Public Function ConvertToSeconds(strTime As String) As Long

    Dim aTimes(2)
    
    aTimes(0) = Val(Left(strTime, InStr(1, strTime, ":") - 1))
    aTimes(1) = Val(Mid(strTime, InStr(1, strTime, ":") + 1, 2))
    aTimes(2) = Val(Right(strTime, 2))
   
    ConvertToSeconds = (aTimes(0) * 3600) + _
        (aTimes(1) * 60) + aTimes(2)
        
End Function

So your example of '150:27:50 ' would return 541670.  After doing any
arithmetic on the values converted to seconds you can then convert the result
back to your time format as a string with:

lngSeconds\3600 & ":" & lngSeconds\60 Mod 60 & ";" & lngSeconds Mod 60


Alternatively the following function, which is really designed for a rather
different purpose, would as it happens also return your current date/time
values to a string of the required format:

Public Function TimeElapsed(dblTotalTime As Double, _
        Optional blnShowDays As Boolean = False) As String

    Const HOURSINDAY = 24
    Dim lngDays As Long
    Dim lngHours As Long
    Dim strMinutesSeconds As String
    
    ' get number of days
    lngDays = Int(dblTotalTime)
    
        ' get minutes and seconds
    strMinutesSeconds = Format(dblTotalTime, ":nn:ss")
    
    'get number of hours
    lngHours = Int(dblTotalTime) * HOURSINDAY + _
        Format(dblTotalTime, "h")
    ' return total elapsed time either as total hours etc
    ' or as days:hours etc
    If blnShowDays Then
        lngHours = lngHours - (lngDays * HOURSINDAY)
        TimeElapsed = lngDays & ":" & Format(lngHours, "00") &
strMinutesSeconds
    Else
        TimeElapsed = Format(lngHours, "#0") & strMinutesSeconds
    End If
    
End Function

Omit the optional second argument when calling it as you don't want to show
the days.  The function would also enable you to do time arithmetic on your
date/time values e.g. in a query

ElapsedTime(SUM([YourTimeField])) would return the sum of the times as a
string in the desired format.

Ken Sheridan
Stafford, England

Lisa W. wrote:
>I have an excel file that I'm importing to Access.  This field consists of 
>"time"...hours, minutes, seconds.  The format in excel is set to "Custom" - 
>[h]:mm:ss.
>
>The data elements look like 150:27:50, 2:30:25, etc...
>
>The time stays the same for some records but others change. For 
>example...150:27:50 becomes 6:27:50 AM when imported.
>
>If someone can help me figure a way to retain the format for all records I 
>would greatly appreciate it. 
>
>Thank You,
>Lisa W.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
KenSheridan
2/11/2010 1:57:37 AM
Reply:

Similar Artilces:

How to export text from Excel to Word #2
Hi, Someone has given me a whole text document written in Excel 2002 and I wish to use it in Word as a doc file. I have copy pasted it but the cells come over too. I can't unformat it so I can manipulate the text as I need to. Can I get the text as plain text without the cells or should I get him to write it all out again this time in Word? I have searched around but now need to ask the experts. Thanks Rock Paste special as text? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law...

split data in a field into two fields
I importated a db from ms works into access. I wold like to seperate the data from one field into two different fields what's the easiest way to do this without resorting to manual entry? Eg department field should be department and location fields 200 1786 becomes 200 in one department field and 1786 in location field Thanks! I would first back up the database and then use an update query. If your values are all consistent with a single space between the Department and Location, your syntax might look like: UPDATE tblMSWorksImport SET Department = Left(DeptLoc, Instr(...

Excel training
I am giving an advanced training session at a pharmaceutical and wondered if anyone can share with me some exercises involving non-conformance, logistics (dispatching and warehousing) templates or files to use with my students. Thanks for any help... Edwin Hi Edwin, See my Pivot Table tutorial at http://edferrero.m6.net -- Ed Ferrero "Edwin Merced" <edwinmerced@coqui.net> wrote in message news:%23aoZbb5oDHA.2312@TK2MSFTNGP12.phx.gbl... > I am giving an advanced training session at a pharmaceutical and wondered if > anyone can share with me some exercises involvin...

Notes Field
Does any one know what table in SQL the notes field in the CRM ties back to? Or does anyone know how i can extract notes along with other fields for an export. -- IT Manager You can access CRM Notes data from the FilteredAnnotation view. The text of the Notes are in the column "NoteText". You will need to join on the ObjectId to the enitity's Id field. For eg: SELECT Ac.[Name], An.NoteText FROM FilteredAccount Ac LEFT JOIN FilteredAnnotation An ON Ac.AccountId = An.AnnotationId HTH, --MD "Melvin F" wrote: > Does any one know what table in SQL the notes f...

How do I concatenate a text field from a form to a "Where" string ?
Hi In the following, "id_Part" is a text field in a report. Me.txtPart is a field on my input form. strWhere is the condition for opening the report. How do I concatenate the Me.txtPart so it is text (surrounded by single quotes) in the strWhere ? In other words, I want the strWhere string to evaluate to the following: id_Part = '95405' (where 95405 is what is entered on the form) Dim strReport As String 'Name of report to open. Dim strField As String 'Name of your date field. Dim strWhere As String 'Where condition for OpenReport. ...

Populate From field from mailto command.
I'm trying to launch a pre-formatted e-mail from IE: mailto:x.x@x.com?cc=y.y@y.com&subject=%20My%20New%E- mail&body=My%20New%20for%20E-mail%20body I can populate cc, subject and body to my satisfaction but I really need to pre-populate the from field as well. from=z.z@z.com doesn't work and shouldn't per this article: http://support.microsoft.com/default.aspx? scid=http://support.microsoft.com:80/support/kb/articles/q1 88/0/19.asp&NoWebContent=1 Any thoughts on how to accomplish this? My requirements are any single command that can produce a preformatted e- mail ...

Customizing Excel's Border Defaults
I'm working with Excel 2000 and use Borders frequently. The preset default border is a thin line, yet I prefer and always change it to the thick line. How can I change the Excel default to the thick line rather than the thin line? I know it's a small problem, but it's driving me crazy changing it all the time. In WORD, I know how to easily "add style default to template", but can't figure it out in Excel. Any help is greatly appreciated! ...

Field Service
Dear All, I'm sill new to Field Service module and I would like to ask a question about how to integrate the RMA of origin "NONE" with SOP? How can i invoice such services to my client? and what setups do i need to check? Note: I'm checking the "Use SOP" in the service setup but no sales invoices are getting created for the client after closing the RMA or only Invoices with Zero amount when i check "allow 0 invoices " in return setup. Thanks Rami Not sure of the dtailes of your questions but to generate an "R" line in Service Call the R...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Handling large excel files
Hi All, I handle large excel files on a daily basis ( 20 - 40 megs) and opening them takes a painful lot of time in my Thinkpad. Most of the time I just need to work with only 1 or 2 work sheet in a file. Is there a way I can make the Excel application load worksheets in an on- demand basis? Thanks, Sundar Sundar There is no build in facility to do this and I suspect any code solution will take just as long. What you could do if there are a regular two or three sheets is have some workbook_close() event code to move those sheets off each time so you have a fresh separate workbook to vie...

Excel links cells
I would like to know if and how to link couple of cells, Let's say I have inserted a code in a cell, this code is corresponding to coulpe of values in a tabele. I would like that Excel would automatically insert the corresponding values of this code (from the table) in different cells on the same raw. Example: Say I punch a code in cell A1 like GH23(which has a value in a table of 15 points and grade 5), then I would like that automatically Excel would insert the value of this code 15 in the cell A2 and the vaule of its grade 5 in cell A3. I would grealty appreciate any help. Tha...

Retainers
Does anyone know the process of receiving retainers and then applying them to the invoice. I need thenm to show up on the invoice. Any help is appreciated. Thank you, Brian In microsoft.public.money, bcarne wrote: >Does anyone know the process of receiving retainers and >then applying them to the invoice. I need thenm to show >up on the invoice. >Any help is appreciated. Not many Business users provide answers. ...

Counting A Field
Hi all, This is one of those things that I should know, but just can't remember right now. I'm creating a database for an extra-curricular program that will allow it to track all of its students. One of the fields that they are tracking is T-Shirt Size. [tshirt] The T-Shirt sizes are a drop-down menu that they are able to change if they need to. The values are contained in a separate table. I've got a report which shows all the students by class and also by division. I'm trying to figure out how to have the report count how many of each size of shirt is needed. W...

Formatting
I am working in Microcsoft Publisher.I have typed in text and then inserted a picture. How do I get the text to wrap around the picture. When working in Word I could just insert a picture and the text could be wrapped around it. In publisher the text is confined to a text box. Whatever I seem to do the picture remains the same only the size of the text box alters and text will not wrap aound the picture.I have tried using edit points but have got in a real mudle I am desperate now. Any help please? What version Publisher? Are you sending the image to the front? Click the dog icon, (picture t...

No fields for the cost center # and GL/Project #
On our PO Entry screen, we don't have a place on the form to add a cost center # or GL/Project #. Does anyone else have this problem? Any solutions? Thanks You could get Extender and add a window for these fields. Extender is incredibly easy to use and they have report dictionaries for the various modules that include the fields you would need to add to your reports. Also, on the purchasing vendor detail entry window are a couple of fields you might be able to use as your own. On the screen I'm looking at is a Contract Number which you could use for Project Number and Confirm ...

Excel Program #3
Hi, I dun hv an excel program. Where can i get from? For your info i just have microsoft word only. Pls reply asap. Thanks For info on ordering see this website. http://www.microsoft.com/office/programs/default.asp to see the Office products and how to purchase. Note:you probably don't need XL2003 so browse to the "previous versions" section. Check out Ebay for a deal on Office 97 or 2000. If you go this route, make sure there is a licence for the product. If you just want to view or print the Excel file you can Download and install the Excel Viewer from.... http://o...

Excel Virus scans
How do I stop the virus scans every time I open an exel file?? It wastes a lot of time begin 666 Paul S.vcf M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E,[4&%U; T*1DXZ4&%U;"!3 M#0I.24-+3D%-13IP875L8F]C80T*14U!24P[4%)%1CM)3E1%4DY%5#IP875L M8F]C84!B96QL<V]U=&@N;F5T#0I2158Z,C P-3 Q,314,C S-3$T6@T*14Y$ (.E9#05)$#0H` ` end Paul S wrote: > How do I stop the virus scans every time I open an exel file?? It wastes a > lot of time > > tools-options-security-macro security set security level to low. be aware of the risks of turning this off. If setting sec...

how to add a field to a form
Hi, I need to add a field from a table i have not yet included into an existing form... I cannot use the field list as it only offers fields from tables previously included in the form. How can I add fields from a table i have never used for a form before without having to start designing a form from scratch again? The field list will include all the fields in the form's record source. The form is most likely using a query (not a table) as the record source. If that is the case, add the field to the query first, then it will appear in your field list. -- Hope that helps! RBea...

Join Query
Hello, I am using Access 2003 with no ability to switch versions. I have two tables. 1 is the current inventory of Fuel Cards, and the second is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards that exist only in the second table. SO I have list A and List B. I want to find all the entries in B that are not in A. A has a two field Primary key. (Fuel Card # and FuelCard Provider) B does not have a key but for all intents and purposes, it has the same key ( though it is not defined within access). Here is the SQL code the wizard gave me. SELECT ex_FuelCardInpu...

Migrating from Goldmine
Hi there, I'm migrating from Goldmine to CRM 4.0, and will ultimately have csv lists of accounts, contacts, leads, activities etc. In using either import method, it always fails when starting out, as if i import accounts first, when it tries to import the primary contact, since there are no contacts to lookup, it fails. Vice versa if i start by importing contacts, i cant automatically assign a parent customer, as the lookup fails because the accounts aren't imported yet. If i import accounts, then import contacts with parent customers listed, it works, but i've lost my ...

Excel Equivalent of Access "Load" Event?
I have a workbook with a couple charts that for some reason "shrink" each time the file is saved -- some kind of 2007 vs. 2003 problem. To solve it I just wrote a wee macro that resizes the chart to the original, desired dimensions. I attached it to the worksheet's Activate event, which works fine once the workbook is open and you move to that worksheet from some other worksheet. But it DOESN'T run when the workbook first opens; if that worksheet is the first one to appear, it has the shrunken chart, and I have to switch to a different worksheet and then back...

Subform Field Vertical Scroll
Hello, using Access 2003, Windows XP. I have a subform with a field that allows for vertical scrolling as long as I click on the down arrow. Is there a way to just left click and hold the vertical scroll bar and move the mouse down for a real continuous scroll? Right now, if I do this, the field doesn't move until I take my finger off of the mouse. ...

How to add & delete custom field ?
- I added a custom view via the "customize view" - I added a new contact - I clicked on the All Fields tab & then User Defined Fields. My new field wasn't there. - I clicked on New and added it. Now it appeared twice. - I closed the new record w/o saving & went back to Custom view - Customize View - Both my custom fields were there... event though they had the same name !!!??? - I deleted one. - Now I can't find the other one. - I spent 2 hours trying to figgure this out. Can anybody just tell me how to add a custom field to my contacts... see that field when I add a...

'From' field shows current profile
Hi, I want to create post items programatically, I have used extended Mapi to modify the sender name, sender email and date properties. but still when I double click on the FORM field, its displaying me my name and email instead of showing the Sender name and Sender Email any help on this ? thanks Riyaz riyaz <riyaz@riyaz.com> wrote: > I want to create post items programatically, I have used extended > Mapi to modify the sender name, sender email and date properties. but > still when I double click on the FORM field, its displaying me my > name and email instead of...

What exactly is office, excel, and encarta?
Office is a collection of programs including Excel, Word, Outlook, PowerPoint, and Access. Excel is a spreadsheet program that is included in the Office collection. Encarta is an encyclopedia, dictionary, and thesaurus. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Samantha" <Samantha@discussions.microsoft.com> wrote in message news:11F2ABDE-D337-44AC-9A93-EDB079460BD1@microsoft.com... > ...