Excel 2010 and Time Import from Access 1

[MMSMSD]

Hello,
I have an Access database which I've been using successfully since Office 2003. It records dates and times. I created a query and then access that query from Excel, to print it out on specific templates. The only CUSTOM thing is that I have Excel configured to show the TIME in 24-hour format.
I upgraded the computer to Office 2007 and the database and Excel forms continued to work properly. When I upgraded to Office 2010, however, the TIME no longer shows up in the Excel form. The DATES show up fine, so I know the import is working, but the time cells are blank. Everything is still in Access; it's just not making it to the Excel documents. If I open the same document in Excel 2007, on another machine, the time shows up just fine, so I know the links and coding are still there and functional. The problem happens with multiple Excel files, from different queries within the same Access database, so I know it's not just a fluke of one file.
One interesting note: when I look at the imported data, from Excel 2010 using Microsoft Query, the failing fields show up as "1899-12-30 06:40:00" (Bad date but correct time. So the time IS making it over but just not displaying properly. I'm thinking that this is the issue but there's no reason that I can see for the bad date to be there. The DATES, in the Access table, are in SHORT DATE format with no alteration. The TIMES, in the Access table, are in SHORT TIME format, with no alteration.
What changed in Excel 2010 to make this start failing? Is there perhaps a different way you're supposed to import the TIME in Excel 2010 than in Excel 2007? I'm at a loss, so any help would be appreciated.

Thanks,
Michael

 

[SkipVought]

hi

"1899-12-30 06:40:00" (Bad date but correct time.

Not really, but yes! There is a problem. In Excel your TIME value should be STORED as 0.277777778. 0.277777778 can be displayed as 06:40, 6:40 AM or 1/0/1900 06:40:00.

Excel makes a conversion of the TEXT "1899-12-30 06:40:00" to a Date/Time value 0.277777778. HOWEVER, Excel does not like dates prior to 1900, so 1899 causes a TILT.

Make sure that your EXPORT does NOT have the DATE part of the TIME.

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MMSMSD]

Thanks for the quick reply!

The field in the Access table is already set to SHORT TIME, so the query does not contain a date at all. It's almost like Excel 2010 is adding a date (and failing) because one is not there. But that ONLY happens in Excel 2010. When I open the exact same file in Excel 2003 or 2007, it works properly.

Since the query didn't include a date, I tried going into Excel and just telling it to show that cell as TEXT, GENERAL and CUSTOM (h:mm). Nothing! The information still shows up in Microsoft Query but the field is blank. I can type something in that cell and it will show and stay but it won't show the data from Access.

I guess I could try to change the format in the original Access TABLE to GENERAL DATE/TIME and then try to parse the time from it through the query or from Excel, but that seems like the wrong way to resolve the issue when it's only Excel 2010 that's failing.

Of course, you know it mocks me: Showing the last day of the year before Excel would work properly! Grr!!!

Thanks again.

 

[SkipVought]

Did you read and understand the FAQ?

How is your data getting into Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MMSMSD]

Skip,
Yes, I did review the FAQ. I believe I understand it. If I print the query directly from Access, I get values like 6:40, 12:30 and 15:00. These are all proper values that Excel should be able to convert as TIME and display properly. Indeed, under Excel 2003 and Excel 2007, they DO display properly. Access certainly seems to be sending the correct information. As a matter of fact, if I open the query in Access and select EXPORT TO EXCEL, the resultant Excel 2010 file shows the times properly! When I click on a cell in the export file, 15:00 for example, it shows 3:00:00 PM so Excel is definitely seeing it as a valid time.

In the final Excel file, I have an ODC connection to the Access file, which grabs the data from the query and puts it into the necessary cells. The cells have no special formatting, since the data from Access is already in the proper format. Under Excel 2003 and 2007, the times show up properly. Under Excel 2010, the cells are blank.

It seems like the problem is with the way Excel 2010 accesses the query via ODC, since earlier versions work without any modifications at all. There is no date in the query output, so I have no idea where Excel 2010 is getting the year 1899 (or why it would choose an invalid date if none is present.) Thanks.

 

[SkipVought]

Please post the SQL in your query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MMSMSD]

Here's the SQL from the query in Access:

----------------------------------------

SELECT tbl_TimeRecord.TR_Date, tbl_TimeRecord.TR_In1, tbl_TimeRecord.TR_Out1, tbl_TimeRecord.TR_In2, tbl_TimeRecord.TR_Out2, tbl_TimeRecord.TR_Reason
FROM tbl_TimeRecord
WHERE (((tbl_TimeRecord.TR_Submitted)=False))
ORDER BY tbl_TimeRecord.TR_Date;

----------------------------------------

TR_Date is a short date field and imports properly
TR_Reason is a text field and imports properly
TR_In1, TR_Out1, TR_In2 and TR_Out2 are all short time fields and are the ones that fail under Excel 2010 via ODC.

 

[SkipVought]

You would make out better SUMMING the Date and Time field values IF they are related...

SELECT
  TR_Date
, TR_In1+TR_Date
, TR_Out1+TR_Date
, TR_In2+TR_Date
, TR_Out2+TR_Date
, TR_Reason

FROM tbl_TimeRecord

WHERE (((TR_Submitted) = False))

ORDER BY TR_Date;

a Time Value alone is worthless without it's accompanying Date Value, unless it is a DURATION. Are any of these DURATION Values?

The other option woud be to export the DOUBLE value as a pure number that you could the FORMAT as a TIME value.

SELECT
  TR_Date
, CDbl(TR_In1)
, CDbl(TR_Out1)
, CDbl(TR_In2)
, CDbl(TR_Out2)
, TR_Reason

FROM tbl_TimeRecord

WHERE (((TR_Submitted) = False))

ORDER BY TR_Date;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MMSMSD]

Skip,
First, let me thank you for your time and effort to try to figure out this issue. In answer to your direct question, none of these are DURATION values.
Second, thanks for your insight into WHY an alternative query would be advised. You are correct: a time value is not very useful without a date. Also, a time value is not really NEEDED unless you plan to DO something with the value later on. In this case, it's only there for documentation purposes and no further calculations will be made to those fields.
As such, there's no real reason for me to keep these fields as TIME values at all. Since TEXT values carry over just fine, I plan to simply change the field types to TEXT and set the input mask to make sure it's in the format I need. I may investigate the issue further at some point but, for now, this resolves my issue without adversely affecting the database.

Thanks again for your help. You really DID help me figure out a solution, even if it wasn't the one I anticipated.

Michael