Nz function compilation error

[Arts]

My query looks like this:
SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]
FROM Diptera;
I get a compile error.
What sould I do?
DAO 3.6 Object Library is ok. The query worked 2 days ago

 

[James]

Works for me, what exactly is the error message?

James

 

[Baz]

Any broken/missing reference will cause this, not just DAO.

 

[Arts]

The message is: Compile error in query clause Nz([Species], [Subgenus])
(freely translated to English)
--
Thank you.


James skrev:

Works for me, what exactly is the error message?

James

My query looks like this:
SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]
FROM Diptera;
I get a compile error.
What sould I do?
DAO 3.6 Object Library is ok. The query worked 2 days ago

 

[AccessVandal via AccessMonster.com]

Hi,

I would start by removing reserved words in access...like

"Name" rename this to something else.

SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]

Arts wrote:
My query looks like this:
SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]
FROM Diptera;
I get a compile error.
What sould I do?
DAO 3.6 Object Library is ok. The query worked 2 days ago

 

[Arts]

Sorry, the query does not include "Name", this is only a translation into
English. The word I am using is "Art"
--
Thank you.


AccessVandal via AccessMonster.com skrev:

Hi,

I would start by removing reserved words in access...like

"Name" rename this to something else.

SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]

Arts wrote:
My query looks like this:
SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]
FROM Diptera;
I get a compile error.
What sould I do?
DAO 3.6 Object Library is ok. The query worked 2 days ago

 

[Michel Walsh]

Are you still using it from within Access? or from somewhere else?


Have you tried to replace Nz(a, b) by iif( a is null, b, a) ?



Vanderghast, Access MVP

 

[Arts]

I am using it from within Access.
I have tried iif( a is null, b, a) and that works ok.
--
Thank you.


Michel Walsh skrev:

Are you still using it from within Access? or from somewhere else?


Have you tried to replace Nz(a, b) by iif( a is null, b, a) ?



Vanderghast, Access MVP

My query looks like this:
SELECT Diptera.ID, NZ([Species],[Subgenus]) AS Name INTO [synonym]
FROM Diptera;
I get a compile error.
What sould I do?
DAO 3.6 Object Library is ok. The query worked 2 days ago