Date and Is Null Query

[2manyerrors]

Hi.

I have this query that is not working right. I hope someone can help me.

Here is the situation; I have three fields that I would like to run a query on that must fit the requirements.

Fields:
Name
Date Hire
Date Expired

Conditions:
If the name field is blank, then the records will be displayed.
If the hire date is blank or if it is five days prior to the current date then the records will show and continue to show until it is updated.
If the expired date is blank or if it is five days prior to the current date then the records will show and continue to show until it is updated.

For example if the current date is 12/05/09 I would like the query to pull all the dates that are going to expire or hired on 12/01/09 and before.

My current query looks something like this.

SELECT CPP_Contractors.ID, CPP_Contractors.Contractor, CPP_Contractors.Hire_Date, CPP_Contractors.Expire_Date,
FROM CPP_Contractors
WHERE (((CPP_Contractors.Contractor) Is Null)) OR (((CPP_Contractors.Hire_Date) Is Null)) OR (((CPP_Contractors.Hire_Date)<=Date())) OR (((CPP_Contractors.Hire_Date)=Date()+1)) OR (((CPP_Contractors.Hire_Date)=Date()+2)) OR (((CPP_Contractors.Hire_Date)=Date()+3)) OR (((CPP_Contractors.Hire_Date)=Date()+4)) OR (((CPP_Contractors.Hire_Date)=Date()+5)) OR (((CPP_Contractors.Expire_Date) Is Null)) OR (((CPP_Contractors.Expire_Date)<=Date())) OR (((CPP_Contractors.Expire_Date)=Date()+1)) OR (((CPP_Contractors.Expire_Date)=Date()+2)) OR (((CPP_Contractors.Expire_Date)=Date()+3)) OR (((CPP_Contractors.Expire_Date)=Date()+4)) OR (((CPP_Contractors.Expire_Date)=Date()+5));


Right now sometimes the wrong results is still showing up such as if the date is 05/05/09 and I run the query I would not get a list of records even if the hire date is 05/07/09.

I hope I am making sense.

 

[PHV]

if the current date is 12/05/09 I would like the query to pull all the dates that are going to expire or hired on 12/01/09 and before
Something like this ?

SELECT ID, Contractor, Hire_Date, Expire_Date
FROM CPP_Contractors
WHERE Contractor Is Null OR Hire_Date Is Null OR Hire_Date<=Date()-4 OR Expire_Date Is Null OR Expire_Date<=Date()-4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[2manyerrors]

Hi there,

Do you know if Date()-4 is going forward or backwards?

For example if today is 05/15/09 and the statement says Date()-4, does it mean 05/15/09 or 05/20/09?

 

[2manyerrors]

I made a mistake.

I meant to say.

Do you know if Date()-4 is going forward or backwards?For example if today is 05/15/09 and the statement says Date()-4, does it mean 05/10/09 or 05/20/09?

 

[PHV]

It does mean current date - 4 days, ie 2009-05-10.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[2manyerrors]

Thank you very much. I used +5. Thanks again.