export erroe

[awake2424]

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.

 

[wiklendt]

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])

 

[awake2424]

Where do I input this text?

 

[wiklendt]

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.

 

[awake2424]

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

Undefined funtion 'Eval' in expression

 

[wilpeter]

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

 

[boblarson]

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.

 

[wilpeter]

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

 

[awake2424]

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

 

[wilpeter]

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?

 

[awake2424]

The expression is:

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


Thanks.

 

[wilpeter]

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.

 

[awake2424]

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.

 

[wilpeter]

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?

 

[wilpeter]

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.