Is Null or Is Not Null

[frenzied_female]

Hi Everyone,

I am trying to calculate the number of weeks open by using a parameter query. I would also like to include in the query the option to use the finalised date to calculate the number of weeks the ticket took to complete.

To calculate the number of weeks open I have used this parameter, which works well:
=IIf(IsNull([Finalised Date]),DateDiff("ww",[Opened Date],Now()))

I then need to add in if the finalised date is not null to calculate the overall number of weeks the ticket was open, i tried this:
=IIf(IsNull([Finalised Date]),DateDiff("ww",[Opened Date],Now())) Or (IsNotNull([Finalised Date]),DateDiff("ww",[Finalised Date],[Opened Date]))

But the result shows as: #Name?

Could somebody please show me where i am going wrong, i have no idea what to do.

Many thanks,
Kim
:banghead:

 

[MarkK]

There is no such function as IsNotNull()...

=IIf(IsNull([Finalised Date]),DateDiff("ww",[Opened Date],Now())) Or ([COLOR="Red"]IsNotNull[/COLOR]([Finalised Date]),DateDiff("ww",[Finalised Date],[Opened Date]))

hth
Mark

 

[isladogs]

I think the extra code forms the Else part of the IIf code
Try this

=IIf(IsNull([Finalised Date]),DateDiff("ww",[Opened Date],Now()), DateDiff("ww",[Finalised Date],[Opened Date]))

 

[frenzied_female]

Thank you both so much for your fast replies!

Fixed my issue perfectly, i only had to swap the order of the opened date and finalised date that i had around the wrong way so that i didn't get a negative number result.

You guys are the best!

 

[isladogs]

You're welcome

 

[Galaxiom]

In queries, rather than using the VBA IsNull() function it is more efficient to use the SQL expression Is Null.

=IIf([Finalised Date] Is Null,.....

http://allenbrowne.com/QueryPerfIssue.html

 

[The_Doc_Man]

And if it ever comes up, the logical negation is NOT ([Finalized Date] Is Null)

 

[Galaxiom]

And if it ever comes up, the logical negation is NOT ([Finalized Date] Is Null)

Curious.

How is that different from:

[Finalized Date] Is Not Null

?