Time format

[pavewayammo]

Ok here is my problem. I export a table from an Access database into an Excel spreadsheet then in to another Access database. I know it sounds stupid but I can't just link the two Access databases or make any changes to the orginal Access table exporting from.

On export, the time is in 24hr format but on the spreadsheet it removes all the 0's infront of other numbers. For example 0800 comes out as 800 or 0000 come out as 0.

Now I would like to put this back in the the 4 digit 24hr format but to this point I have been able to figure it out. I have tried messing around with the format on the Access table also.

I would go as far as making a macro for the Excel spreadsheet to correct it.

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[DoubleD]

Have you tried using a query to export the data instead of directly from the table. Then you could format the field as text.

I am what I am based on the decisions I have made.

DoubleD

 

[pavewayammo]

The output from the first Access DB is not changable. It is a MDE file and the DB owner has locked it so we can't make changes. Also they don't want a add/change any of it's current features.

I am so close to just linking to the first DB anyway and deal with the heat about it later.

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[DoubleD]

Yep, you've got problems. How would you interpret the following value:

You could import the data into a temporary table, then write a function to interpret the hour values. Something like this:

Function ConvertTo24(intNum as integer) as Date
Dim strFill as String

  Select Case Len(intNum)
    Case 1
      strFill = "000"
    Case 2
      strFill = "00"
    Case 3
      strFill = "0"      
    Case Else
      strFill = ""  
  End Select
  
  ConvertTo24 = FormatDateTime(strFill & intNum, vbShortTime)

End Function

This is only air-code, but it should get you pretty close to what you need.

I am what I am based on the decisions I have made.

DoubleD

 

[pavewayammo]

I'll give it a shot when I return to work monday. I'll keep you posted.

paveway

http://www.deagel.com/img/lib/1v9961.jpg

Looking for help check the FAQ's first then do a search then ask. Worked for me.