Return a "Blank"

[JSimoncelli]

All,

The query I am working with is used to track training courses. Some of these courses have a re-occurring requirement for example Fire Extinguisher training may be required ever year (an interval of 356 days). There can be several intervals types 90, 120, 365 days or no re-occurring requirement.

The three fields I am looking at are the Interval, TrainingCourseCompleted and the NextDueDate. The NextDueDate is created by the following:

NextDueDate: DateAdd("d",+[Interval],[TrainingCourseCompleted])

This works fine, however the courses that do not have an Interval the NextDueDate returns the word “#Error” in the field. What I would like is for the field to return a Blank (Null) or maybe the word “None” whatever works best..

What I am thinking of but don’t have a handle on is the following:

IIf Interval is Null Then NextDueDate: DateAdd("d",+[Interval],[TrainingCourseCompleted]) ells NextDueDate: “**” Insert here whatever is required to return a bank or the word None.

I know the syntax is all wrong, this is what I need help with.

For the courses that do not have an Interval, I would like the NextDueDate to return a Blank or the word None what ever works..

Thank in advance

John

 

[CJ_London]

try

NextDueDate: iif(interval is null, null,DateAdd("d",+[Interval],[TrainingCourseCompleted])

this will return a blank

 

[JSimoncelli]

Wow thanks works great, I believe I was on the right track but lack the proper experience with the syntax.

Again thanks very much.

John

 

[JSimoncelli]

Oh one minor fix, had to add an ) to the end, was missing the closing ).

 

[CJ_London]

sorry - comes with responding too quickly, but well spotted