Upload from Excel sheet

[janz]

Can any body point me in the right direction? I'm completely out of ideas.

I import an excel sheet with the following command

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "data", root & "tmp_Query_Output.xls", True

Works fine but for one issue.
I have 6 dates with time in this sheet. 4 come over fine 2 come over with only the date.

I do format the excel sheet seperately before the inputs to the proper date format. The database that receives the data is also set to general date.
But still 4 ok 2 only the date no time.

If I however attach the excel sheet to the db and then use an append query to the same table it works fine.
However I still would like the Transferspreadsheet cmd or at least understand why it doesn't work.

Any body??

 

[Trevor G]

This may not be relevant but in the Database in the table have you set the format for the field?

Access would look to use:

Date and Time as follows:
dd/mmmm/yyyy hh:nn

 

[janz]

Trevor G,

Thanks that did the trick. I never thougt about that I was always under the impression that setting the format field to General date would do the trick.
But it didn't. It took me so much time to solve this issue that the sollution is almost embarrassing easy. Thanks again.

Ps.
Still puzzles me why some fields did and others didn't but that is one for in the crazy Microsoft box.

 

[Trevor G]

It is worth spending a little time setting the formats for each field where you think there might be a conflict or problems. Even with things like postal codes (as we know in the UK the postal system requires them to be in upper case) you can force anything to become a capital by using the greater than symbol > and anything typed in a memo field like basic notes to lower case again using the < less than symbol in the format for each field.

I'm pleased to read it has worked for you.