I am getting an error trying to export an Access 2003 query to excel. It is one of the column (contains an expression).

The error is:

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria in expression. Thanks.

does the query work when you run it in access (without exporting it?) often, you may need to "evaluate" the expression for exporting. this would look something like:

Eval([yourexpressionhere])

Where do I input this text?

whereever you have the expression you told us you had in your query, change:

YourExpression

to

Eval(YourExpression)

and see if that works... i'm not sure if it works in query expressions, but i know it works in query criteria - just try it and see.

It does not work... the error that appears on the screen is.

Undefined funtion 'Eval' in expression

This is a 2007 function. Are you using 2007 or an earlier version of Access?

This is a 2007 function. Are you using 2007 or an earlier version of Access?

??? What function are you talking about? EVAL? Eval has existed long before 2007.

Whoops! Forget that question, it works even in 2002.

I am using Access 2003 and it is not working. The error above is what is displayed. Thanks.

Can you supply the Expression/formula you are using that causes the error message please. Also, you understand that the result of the formula is what is being exported. Is it a date, a number, a text value that you expect?

The expression is:

Time: (DateDiff("d",[Order Date],[Report Date])) and it outputs a number.


Thanks.

I just tried it in a Select Query (even included the parentheses and used the reserved word "Time" as the calculated field) and it worked fine. I then used Analyze with Excel and got the same result. I'm using Access 2000 SR3. How did you do the export? I even tried different Format for the two date fields without change in result.

On the toolbar I goto Data.... Import New Database Query and point it to the directory in which the Access Database resides. When I run the query in Access it works fine. I also have no problem exporting all columns in the query except the calculated column.

I enter the TAT: (DateDiff("d",[Order Date],[Report Date])) in the Field box in the query and have the Show box checked. Thanks.

Reading between the lines, you are IMPORTING to EXCEL from ACCESS. I based it on EXPORTING from ACCESS to EXCEL. That assumes then that your are not allowing Access to dictate the field formats from its own field data types. Am I on the right track?

Instead of importing from "New Database Query", use the "Import Data" choice. It worked for me even when I placed the result in a group of cells that had been pre-formatted to Number. By the way, in this way you don't have to reenter the query formula...it's already in the query you choose from the list of tables/queries.