Using Nz() in MS Access

[Anonymous]

Hi

I have created a query (view) in MS Access that does exactly what I want. Unfortunately, I can't get QlikView to use this query to load the data as it doesn't like the Nz() function, which I use to replace my EndDate field with the current date using Now(). I found another thread dealing with this, but haven't managed to get that to work. Below is what I am trying based on the other thread. Can anyone help?

Thanks.

Joao.

LOAD

Universe.UniverseName,

Funds.FundName,

Returns.ReturnDate,

Returns.Return,

Funds_Universe.Weight,

Funds_Universe.StartDate,

if(Len(Funds_Universe.EndDate)=0,now(),Funds_Universe.EndDate) as SomeDate;



SELECT
Universe.UniverseName,

Funds.FundName,

Returns.ReturnDate,

Returns.Return,

Funds_Universe.Weight,

Funds_Universe.StartDate,

Funds_Universe.EndDate

FROM (Funds INNER JOIN (Universe INNER JOIN Funds_Universe

ON Universe.UniverseID = Funds_Universe.UniverseID)

ON Funds.FundID = Funds_Universe.FundID) INNER JOIN Returns ON Funds.FundID = Returns.FundID

WHERE (Returns.ReturnDate>=Funds_Universe.StartDate) AND (Returns.ReturnDate<=Funds_Universe.EndDate);

[Anonymous]

For completeness, below is my query in MS Access:

SELECT Universe.UniverseName, Funds.FundName, Returns.ReturnDate, Returns.Return, Funds_Universe.Weight

FROM (Funds INNER JOIN (Universe INNER JOIN Funds_Universe ON Universe.UniverseID = Funds_Universe.UniverseID) ON Funds.FundID = Funds_Universe.FundID) INNER JOIN Returns ON Funds.FundID = Returns.FundID

WHERE (Returns.ReturnDate>=Funds_Universe.StartDate) AND (Returns.ReturnDate<=Nz(Funds_Universe.EndDate,now()));

[Gysbert_Wassenaar · Accepted solution]

I don't see anything wrong. It looks ok to me. Well, maybe use len(trim(Funds_Universe.EndDate)) instead of just len. Just to make sure a stray space doesn't mess things up. 

You could replace the Nz in the select statement with IIF(ISNULL(Funds_Universe.EndDate), now(), Funds_Universe.EndDate). That should work afaik.

[Anonymous]

Hi Gysbert

Thanks a million. The second suggestion seems to work perfectly.

Joao.