Execute expression in query if not null

nuttychick

Registered User.
Local time
Today, 06:30
Joined
Jan 16, 2004
Messages
84
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
 
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:D

Brian
 
Last edited:
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
 
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
 
Brianwarnock said:
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:D

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 :)
 
Thanks Workmad3, I think in the end it is the NZ function she requires.

Brian
 
Thank you...but.....

:D 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?

Code:
Planned Landing Slot End: DateAdd("d",nz([firstduration],DateDiff("d",[firstlandingslot],[firstViabilityReport])),[firstlandingslot])
 
nuttychick said:
:D 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:confused: :confused:

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

Brian
 

Users who are viewing this thread

Back
Top Bottom