IIF and ISNull in a Query (1 Viewer)

[selenau837]

Hi guys,

I have a mod called EntryAge. It requires two arguments. A start date and an End date.

I'm trying to use it in a Query, but I am running into problems because there are times when the Start date and the end date are both Blank.

I've tried this nested statement and it keeps telling me. Contains invalid syntax or I need to enclose it in quotes.

I've copy and pasted my code below. Can someone please help me figure out what is wrong with this silly thing.

intSecondaryTime: EntryAge(IIF(ISNull([dtmSecondaryReceived]),0,[dtmSecondaryReceived])),(IIF(ISNull([dtmSecondaryComplete]),0,[dtmSecondaryComplete])))

What I am trying to say is if the received date is null make it 0 or use the date given, and if the complete date is null use 0 or use the complete date.

It then will perform the calculation that the Entry Date mod does.

If you want I can copy and past the Entry Date Mod. If that will help.

 

[KenHigg]

intSecondaryTime: EntryAge(nz([dtmSecondaryReceived]),nz([dtmSecondaryComplete]))

???

 

[selenau837]

intSecondaryTime: EntryAge(nz([dtmSecondaryReceived]),nz([dtmSecondaryComplete]))

???

Yes, I tried that too, I think the problem is that when it returns a 0 it goes to the defualt date/time of 12:00 am. I think that is playing havoc with my Entry Age Mod.

 

[KenHigg]

You can account for the 0 in your function can't you? i.e: If either = 0 then simple set the result to zero befor it goes through the date stuff?

 

[selenau837]

I've got it working now. Here is the final code.

intSecondaryTime: IIf(IsNull([dtmSecondaryReceived]),0,IIf(IsNull([dtmSecondaryComplete]),0,EntryAge([dtmSecondaryReceived],[dtmSecondaryComplete])))

Thanks,