Exporting tbl to Excel gives wrong data!

[DaniBoy]

Hello guys/gals,
Am back with a question agian!!, well I am exporting a table to Excel, I do it the easy way by going to file export ect. Once the table is exported and I go check the spreadsheet, all my fields are good except one text field.

This text field I have is a combobox on the table/form with the a value list of a fiscal year "2001-02;2002-03;2003-04" when I check the spreadsheet I get regular numbers for this field "36923" I wonder why this happens. Its like is reading something else on the field.

Any suggestions!!!

DaniBoy

 

[bradcccs]

Access stores the date in integer (numerical) format from an origianal reference date. Have heard (as a rumour) that it is Big Bill's birthday (just kidding).

I also belive that excel deals with the date in a similar format. Try formatting the destination cell as a date format (format / cells etc).

eg: 09/08/02 (dd/mm/yy) is stored by excel as 37477 but is displayed according to the cell formatting.

HTH

Brad.

 

[DaniBoy]

Tried that Thanks

Hello
I tried that but it does not fixes my problem, it actualy gives me the date and thats not right.
I am tranfering a data field that is a text field. For example: the text field has a value of "2002-03" I should get the same "text" value on the spreadsheet right? Well is not doing that, I have no idea what is going on, I have tried so many things but it does not export the "2002-03" text field like it suppose to be.

thanks for the help though,

DaniBoy

 

[bradcccs]

What datatype is specified for your field?

You have mentioned "text field" in your post and therefore I would assume you have specified it as a text field.

I have tried to replicate your scenario, and if stored in access as text "2002-03" it is imported as text "2002-03" to excel.

Mind you, I have heard of weirder things happening.

Brad.

 

[DaniBoy]

Tell me about it!!! jejeje

Yes, the field is text 360 degrees!!! I tried exporting the table as a query and it worked fine, so I guess am gonna do it that way, but it is wierd that is happening like that!!!

Thank for all your help

DaniBoy

 

[bradcccs]

Lame excuse for help. I can't really take any credit for that.

Sorry.

 

[Freddy]

DaniBoy

Try using the the format() command to convert the date field to a formatted text string before exporting.

 

[The_Doc_Man]

No, don't use format() of a date field from a query. What you are selecting must never be a date in the first place.

One of your sample strings, "2002-03" could well be a date : some day (probably the 1st) of March of 2002

You intend to specify your field as a range of dates, which means that AS A DATE FIELD, it cannot be expressed in one field. There is no elementary field type that is a range of dates.

But if it is treated as text from the start, there is no ambiguity in what you mean or how it should be presented.