Exporting a Query to Excel - Date Problem 3

[belovedcej]

I have a query that the user frequently exports to excel. It's a linked db to SQL server, if that makes any difference.

We use "1/1/1900" as a default in date fields when the user does not have a date, so a number of our queries show that date. What's odd is that when exported to excel, anything with a date of "1/1/1900" appears with a date of "1/2/1900". (by the way, they are datetime fields, so the value is actually 1/1/1900 00:00:00.)

It makes us wonder at the integrity of the other dates when, though they appear to be right so far.

Does anyone have any ideas as to why this is happening?

 

[SkipVought]

Hi,

Date/Time is a number. Date is the integral part; Time is the fractional part. The display of the value can be FORMATTED to show Date and/or Time in any number if different formats that have nothing at all to do with the actual value.

The VALUE of 1/1/1900 in Excel is 1.

The VALUE of 1/1/1900 in Access is 2. 12/31/1899 has a value of 1.

BTW, Access can handle dates prior to 1900. Excel cannot.

Skip,

_[red][/red]

 

[PaulBricker]

If you need to see the date 1/1/1900, you could manipulate your dates using the Format Function to convert your dates and it will show in Excel the way you want.

Skip - nice explaination. I was aware of Access's handling of the dates, but did not know it was different for Excel. Have a star.

Paul

 

[belovedcej]

I thought it had something to do with the integer value.

But then my question is this - won't that affect later dates since it's a number? Wouldn't all dates be "one day off" when sending them to excel?

 

[SkipVought]

As Paul suggested, Convert the date to a STRING using the Format function.

Then in Excel, you'll have to convert the string back to a date using the DATEVALUE spreadsheet function, assmuing that the date string is formatted in the recognizable REGIONAL format.

Skip,

_[red][/red]

 

[belovedcej]

Wow - ok.

This is huge. I have over one-hundred queries and reports to do this for. Nobody ever noticed the dates were off before, either.

Thanks for the help.

 

[belovedcej]

ok - now I'm confused.

I just went and tested exporting some queries to excel. No problem - all the dates are right. It's only the 1/1/1900 that seems to change. But that doesn't make sense to me. Why would it work for some and not work for others?

I can just create a null value where it gives 1/1/1900 - that's not a big deal. I just want to make sure the data is still right when it is exported. Do you know why it would be acting inconsistently?

And I don't want to go through hundreds of reports to set up formatting. Not just because it's a lot - but also because then everything would sort in string order instead of date order, and the users don't want "0" behind single digit months and days.

 

[PaulBricker]

The problem seems to clear itself after 2/28/1900 which, in Excel, shows as 2/29/1900, but there was no leap year that year I don't think because if you try and put 2/29/1900 in as a date in Access, it won't let you and 3/1/1900 shows as 3/1/1900. You could use an IIF statement in Excel to show the dates as you want. Something like access...IIF(Datefield = 1/2/1900, 1/1/1900, DateField). I don't know if it's worth the effort. If you know the current dates are correct, then it seems the rest can just be 1/2/1900.

Paul

 

[SkipVought]

I just did a query from Excel to an Access db and got the correct 1/1/1900 dates.

Can you post your code for the query resultset export to excel?

Skip,

_[red][/red]

 

[belovedcej]

The table is actually a linked table which looks at a view in sql server.

Here is a very trimmed version of the query:

SELECT DISTINCT Case_Number, Event, EventDate
FROM dbo_OAS_ADH_Event_V 
WHERE (((dbo_OAS_ADH_Event_V.EventDate)<Date())

The user runs the query and then goes to the menu and chooses "export to Excel." No vba at all - this was a "quick and dirty" reporting tool.

Paul's answer makes a lot of sense- it explains why it is happening and reassures me that the "real" dates will all be correct.

Thanks so much for the help, guys!

 

[SkipVought]

Hmmmmmm???

My Query export to Excel converted 1/1/1900, 1/31/1900, correctly.

???

Skip,

_[red][/red]

 

[PaulBricker]

My query export showed the dates as belovedcej was seeing them. Version specific problem? Table(date) format issue? I'm just using Access, no sql server backend. I formatted my dates mm/dd/yyyy hh:mm:ss and I'm using Access 2003(2000 file format).
Beats me.


Paul

 

[belovedcej]

I am also using Access 2003 formatted as 2000. My date format was m/d/yy.

 

[SkipVought]

Access 2003 SP2
Excel 2003 SP2

Skip,

_[red][/red]