overcoming blank date fields when used in calculated field in a query

[grapevine]

I have three fields,

coursestartdate, courseplannedenddate and a calculated field which uses both date fields.

Doc6Overdue: IIf(DateAdd("d",(([courseplannedenddate]-[coursestartdate])/5),[coursestartdate])<=Date(),"overdue","notoverdue").

This query works well and correctly gives overdue or notoverdue or if there is no date it gives Error.
I need to put overdue in the criteria line as I only need to see the overdue records, but when a date is missing I get he message Data mismatch in criteria expression. I really dont know where to start in solving this,how can I overcome this happening should a date be missing?
Any help greatly appreciated
Marion

 

[speakers_86]

At the table level you can set the date field to be required. Or you could set the default value for the date fields at the table level. Or at the query level, include the nz function on your dates. When nz is used, nulls will be replaced with whatever you specify.

Ex of nz:
Date: nz([NameOfYourDateFieldHere],"Value if null")

Although, I wouldnt use the name date for the label of a field in a query.

 

[Brianwarnock]

You could also filter out the records where Date is null by putting Is Not Null in the criteria for the dates.

Brian

 

[speakers_86]

You could also filter out the records where Date is null by putting Is Not Null in the criteria for the dates.

Brian

hahaha. The simplest answer and I missed it. Wow.

 

[grapevine]

Thank you for your replies.

I have tried is not null, but when I tested this it came back with the error. A question though. If data is entered into a field and then deleted is it a null field? As is not null was working but then seemed to stop working and I wondered if it was because I deleted some dates to test the query.

Speakers86 - I have used your expression and successfully entered a date into the new field which i have created, but I dont appear to be able to then do a calculated field on the created fields. The answer comes up as #error. I may just put some default dates in the table.

Should I be able to do my original calculation on created fields?
Are fields where data has been deleted null?

I look forward to hearing from you.
Marion

 

[Brianwarnock]

Yes a deleted field is null, don't want to sound cheeky or rude but you did delete not blank the field, yeah of course you did.
I just don't know why it would stop working.

Brian

 

[gemma-the-husky]

where/how are you putting the isnotnull criteria?

that may be the issue

----------
it is hard to tell whether something is blank or null

but if its a date, then a blank date has to be null. i think

text strings are the hardest to tell because an apparent blank may be either a null, or a zero length string, or even maybe a non-zero length string of spaces

 

[grapevine]

Thank you for all for your replies and I apologise for not replying earlier but other demands have kept me away.

Brian - not sure what you mean or whether I have done it correctly. After typing a date in my table and running the query, I then returned to the table and highlighted the date and pressed delete.

Gemma - I have been putting "is not null" in the criteria line under the CourseStartDate and CoursePlannedEndDate query boxes.

Unfortunately, I will not be able to devote any time to this today, but will have tomorrow to investigate again and spend a decent amount of time of this.

Any other suggestions always grateful and please dont worry about being cheeky or asking whether I have done what might seem blindingly obvious to you! Like many of you I suspect I am learning on the job.

Many thanks

 

[Brianwarnock]

Sounds like you are doing everything correctly but just to check the criteria is
is not null
no quotes, and it will capitalise to
Is Not Null
if everything is ok.

Brian

PS Hoping to spend tomorrow walking so may not get back on till Friday.