News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, March 07, 2007

Export mixed type data to Excel

While we have already knew how to deal with Import or Link mixed type data from Excel, here a trick on export. Today I got a screenshot from customer, who tried to export Access report to Excel and got “Type mismatch” error. Screenshot showed a typical mixed data type column with document numbers, some of them with numbers only, and other started with letters. So export engine made an assumption on a first row that column type is numeric, and later produced the error on non-numeric data. Well, this I can’t say for sure - this was my guess. Anyway – I have opened report’s underlying query and added a space at the end of document number field: Select …. DocNum & “ “ as DocNum, .... And error gone!

Technorati tags: , , ,

Labels: , , ,


Anonymous Anonymous said...

please help me. Im a new user of access 2003. Im making a database for market owners, monthly rentals and payments. I dont know what to do in order to compute and make a statement using the data encoded at tables. I want to know their balance in a specific time. is there a formula? where would i go? can you teach me step by step. thanks

9:16 AM  
Blogger Alex Dybenko said...

find a column, which cause this error, and then to add space at the end

6:35 PM  
Blogger redpizza said...

Sounds good but if the offending field is a multi-valued list field you get this error:
The multi-valued field 'Eval.Fulfilment' is not valid in the expression 'Eval.Fulfilment & " ".

6:15 PM  
Blogger Alex Dybenko said...

try to find a valid expression, which converts your multi-valued list field to string, and ue it. perhaps subquery or function

6:43 PM  
Blogger Office Setup Help said...

Thank you for sharing insights. User guides at www.Office.Com/Setup covers all this and many more. Looking forward to apply this newly acquired knowledge.

10:34 PM  

Post a Comment

<< Home