Incorrect Column expression Error when I use Is Null or Yes Criteria.

[Shilabrow]

I get the error Incorrect Column expression .... when I use the ( Is Null or "Yes" ) as criteria. The issue is the error only occurs when I import records/data using MS Query connection to Excel from Access but when I use the query in Access, I get the result without error. Is there a different way to query for yes or blank criteria and successfully get my query out to excel without error. All help appreciated. Thanks much!

[June7]

What do you mean by 'import query using MS Query connection to Excel from Access'? Do you mean import records?

[Shilabrow]

yes - sorry import record or data. All help appreciated. Thanks!

[June7]

Okay, now I don't understand using criteria for an import. What do you mean by 'MS Query connection to Excel'? What do you mean by 'get query out to Excel'? Apparently I have never done anything like what you are trying to describe.

[Shilabrow]

I am trying to import data to excel from access. The query works at first and I get data imported successfully into Excel but when I use the criteria where WHERE (((Products.Category) Is Null Or (Products.Category)="oil")) that is when the error comes up as "Incorrect Column expression".

SELECT Orders.[Order Date], Products.[Product Name], Products.Category, [Quantity]*[Unit Price] AS Amount
FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ID = [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order ID]
WHERE (((Products.Category) Is Null Or (Products.Category)="oil"))
ORDER BY Orders.[Order Date], Products.[Product Name];

Is there another way to query Is null or Oil so that I can avoid the error or what am I doing wrong. Thanks June7 for your help - I appreciate this.

[June7]

You want to export from Access to Excel?

No idea. The query looks fine to me. I have known Excel to choke on Nz() used in expressions in Access query but your criteria should work.

Your original post said criteria would be "Yes" - not seeing that in your SQL.

Did you try with just one of the criteria at a time?

[Shilabrow]

Yes the criteria works if used one at a time, But when I combine the two that is when I get the error. Also I notice that I get error importing when parameters are used such as "between and" for dates. Thanks June, I was just wondering if there is a different way to pull the same data and changing the criteria so that I can still get my Oil and Null data.

[June7]

I tested exporting query that had multiple criteria like yours and it worked without error. Used the Access Export wizard.

Also tested with BETWEEN AND on a date field and that works.

Cannot replicate the issue.

[Shilabrow]

I see you mentioned Access Export Wizard, I use the Excel MS Query connection. I have never tried exporting from Access - Is there a link you can refer me to as to try this from Access (Step By Step)? I appreciate your relentless effort in helping me get result I want. Thanks!

I tested exporting query that had multiple criteria like yours and it worked without error. Used the Access Export wizard.

Also tested with BETWEEN AND on a date field and that works.

Cannot replicate the issue.

[June7]

So you were using Excel to pull data from Access and not pushing the data out from Access? I've only experimented with linking from Excel side, nothing implemented.

Try searching Access Help or Bing for info on Access export wizard. That's what I would have to do to get you a referral.

Or click the ribbon and experiment. Look at the External Data tab.