Execute expression in query if not null (1 Viewer)

[nuttychick]

Can anyone help...

I am writing a query in which, amongst other things, I also want to perform a dateadd calculation.

I have the following

expr:dateadd("d", [lastduration], [lastlandingslot])

This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null.
I have ran the code below sucessfully.
expr:dateadd("d", 1,[lastlandingslot])

How can I get this to execute only if the [lastduration] is not null??

Tried to search but network is really slow

 

[Brianwarnock]

Must be something like IIf(isnotNull([lastduration],dateadd("d", [lastduration], [lastlandingslot]),null situation)

or maybe IIf(isNull([lastduration],null situation,dateadd("d", [lastduration], [lastlandingslot]))

Yes quick check showed IsNotNull is not valid, don't know where I dug that thought from, typical of Microsoft not to give the user what he really wants

Brian

 

[nuttychick]

could be me....but

Hi Brian, sorry - I tried both of these with no joy...can you advise further.

Think its the null situation that its failing on...what sort of thing can I put in here? If firstduration is null can I make it = 0 for the query?


Thanks

 

[Brianwarnock]

Do you mean

IIf(isNull([lastduration],0,dateadd("d", [lastduration], [lastlandingslot]))

or maybe Dateadd("d",nz([lastduration],0),[lastlandingslot)

The latter causes 0 to be added to lastlandingslot

Brian

 

[workmad3]

Must be something like IIf(isnotNull([lastduration],dateadd("d", [lastduration], [lastlandingslot]),null situation)

or maybe IIf(isNull([lastduration],null situation,dateadd("d", [lastduration], [lastlandingslot]))

Yes quick check showed IsNotNull is not valid, don't know where I dug that thought from, typical of Microsoft not to give the user what he really wants

Brian

I think IsNotNull and IsNotNumeric and so on are new functions for VB.NET or possibly the next planned version It could be from there that you got the idea... you can just use Not IsNull to get the same effect though, or wrap that around a function called IsNotNull to do it

 

[Brianwarnock]

Thanks Workmad3, I think in the end it is the NZ function she requires.

Brian

 

[nuttychick]

Thank you...but.....

Thank you !

The nz function worked.
However...I now have the problem of the chance of the firstViabilityReport date being null....NOOOO

Any way of saying if the firstViabilityReport date is also null then just stop. Hopefully it wont be too much of an issue - at the moment the database is just not up to date, users have only just been given these fields to populate.

Any ideas?

Planned Landing Slot End: DateAdd("d",nz([firstduration],DateDiff("d",[firstlandingslot],[firstViabilityReport])),[firstlandingslot])

 

[Brianwarnock]

Thank you !

However...I now have the problem of the chance of the firstViabilityReport date being null....NOOOO

Any way of saying if the firstViabilityReport date is also null then just stop.

firstViabilityReport date ?????

then just stop

I think that you are going to have to give a few explanations here before anybody can help.

Brian