Invalid Procedure Call

[bigal.nz]

Hello,

Why is it that this query is OK:

SELECT *
FROM DATABASE
WHERE (
Child1DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child2DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child3DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child4DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child5DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child6DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
ChkUnborn="Yes"
);

But this query (I added the NOT) give a error Invalid Procedure Call:

SELECT *
FROM DATABASE
WHERE NOT (
Child1DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child2DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child3DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child4DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child5DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
Child6DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR
ChkUnborn="Yes"
);

It is to do with the ChkUnborn="Yes" - if I remove that the second query with the not runs ok.

Help.

Thanks

Al

 

[bigal.nz]

I actually wonder if its to do with empty fields.

for example if all the childs dates of births are empty then:

Child1DOB between today and today-5 years is fine because it returns false.

But if try:

NOT
Child1DOB between today and today -5 years I want it to return true (even though there is no date in Child1DOB.

So I need to test for null as well as between?

 

[pbaldy]

I think the DateAdd() function will throw an error if the date field is Null. You might try wrapping that in the NZ() function.