Query runs fine but report doesnt work

[endri81]

Hi
When I run query :

SELECT datepart("q",[Data e fillimit te tremujorit]) & year([Data e fillimit te tremujorit]) AS PeriodQuarter, clng(IIf(left(PeriodQuarter,1) In (2,3,4),year([Data e fillimit te tremujorit]),year([Data e fillimit te tremujorit])-1)) AS FiscalYear, *
FROM Konsumi_Disaperiudha1_Gimi;
it runs fine and shows table result
but when I try to build a report by using this query I get message:

"The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"

and if I click Ok and try to preview report I get
Invalid use of Null

I see that in Dataview there are some #ERROR fields as shown in picture attached.
Maybe this is the cause.
How to get rid of this issues?

Please help

[alansidman]

Wherever you reference field [Data e fillimit te tremujorit] that is null, you will get an error in the calculation. You will need to tell Access what to do when it encounters a null field. This is accomplished using the NZ function. Look at this for explanation of NZ

http://www.techonthenet.com/access/f...dvanced/nz.php

You will have to wrap any calculation of this field in an NZ function.

[endri81]

Thx this seems interesting and possible solution but not working for my query :

SELECT datepart("q",[Data e fillimit te tremujorit]) & year([Data e fillimit te tremujorit]) AS PeriodQuarter, clng(IIf(left(PeriodQuarter,1) In (2,3,4),year([Data e fillimit te tremujorit]),year([Data e fillimit te tremujorit])-1)) AS FiscalYear, *
FROM Konsumi_Disaperiudha1_Gimi;

Maybe I am making it wrong ,can you please help on modification of the specific query?
Regards

[alansidman]

If the field is empty that you are trying to use as a base for the calculation, what do you want Access to do? I can help you but you need to address that issue first. What value do you want if the the field is null?

SELECT datepart("q",[Data e fillimit te tremujorit]) & year([Data e fillimit te tremujorit]) AS PeriodQuarter, clng(IIf(left(PeriodQuarter,1) In (2,3,4),year([Data e fillimit te tremujorit]),year(NZ([Data e fillimit te tremujorit]),Need Value Here)-1)) AS FiscalYear, *
FROM Konsumi_Disaperiudha1_Gimi;

Without the NZ function, you will continue to get errors as Access doesn't know what to do with the Null field. It cannot do a calculation as you are asking on a Null.

[endri81]

Thx for the very fast answer
I changed value Need Value Here with "0" but still giving error Syntax Error missing operator??
What to do?
Regards