Calc. Field Not Exporting...

[mission2java_78]

I have a report in which sometimes this report is exported to excel. One field "End Warranty" which happens to be a calculated field doesnt seem to get exported correctly. When I send the report to any format including pdf its fine it shows a date. But when I export the field to excel it doesnt show a date it just shows some numbers...(37257, mainly that number...not sure why).

Jon

 

[Fornatian]

M2J,

Are you sure it is not exporting, or it is export the dates in their native fashion. I suspect that the reason you are getting 37257 is because this represents 1st Jan 2002 in computer lingo, 37257 days since 1900(or 1904 depending on the date system employed).

Anyway, try formatting your calculated column using the format function, e.g. Format(WhateverCalculation,"dd-mm-yy")

Hope that was useful

 

[Jason]

M2J

Fornation was right, the date gets exported as a system date. All that you need to do is select your entire column in Excel and then set the format of the cells to date. Unless you do it in code, (opening an instance of Excel from within VBA in Access and then doing the formatting behind the scenes) you will always come across this issue when exporting dates using the export to excel option.. You might just want to drag the "Analyse it with Excel", (out of Tools, Office Links) menu item onto a custom, (or the regular) toolbar. Sometimes this keeps the date format setting.

J

 

[mission2java_78]

I am using format in my code though:
EndWarranty: IIf(IsNull([WarrantyStart]),"",IIf(IsNull([Duration]) And IsNull([Days]),"",IIf(Not IsNull([Duration]) And Not IsNull([Days]),Format(DateAdd("d",[Days],Format(DateAdd("m",[Duration],[WarrantyStart]),"dd-mmm-yy")),"dd-mmm-yy"),IIf(IsNull([Days]),Format(DateAdd("m",[Duration],[WarrantyStart]),"dd-mmm-yy"),Format(DateAdd("d",[Days],[WarrantyStart]),"dd-mmm-yy")))))

Jason what are you refering to ?

Jon

 

[Rich]

Surely the Format function is returning a string, not a Date value?

 

[mission2java_78]

Hmm...I'd prolly have to throw in a convert wrap around dont I ;-).

Access help!!!
:-d

 

[mission2java_78]

Wrapped up format with CDate...but still the same problem. I guess Ill just highlight the complete column in Excel and change the format there...users just hate that though..
Thanks anyhow guys.
Jon

 

[Fornatian]

M2J,

Having looked at your formula, unless I have lost the plot somewhere along my train of thought, I think it can be shortened to this...

IIf(IsNull([WarrantyStart]) OR(IsNull([Duration]) And IsNull([Days])),"",
Format(DateAdd("d",Nz([Days],0),DateAdd("m",Nz([Duration],0),[WarrantyStart]),”dd-mmm-yy”))

 

[mission2java_78]

Yep, the code is old and sloopy been a while. I knew I could shorten it since a few of the IIF's were pointing to "". But my main concern was this field in excel...which I've yet to figure out .

Jon