Dreaded date filter!

[Sam Summers]

Hi guys,

Been on this for days and searched and tried all kinds of things but i am still getting a load of mince in my results?

This is my SQL:

SELECT Job.JobID, Job.[Job Number], Job.PMDN, Job.JobName, Job.Location, Job.Client, Job.Description, Job.Type, Job.Status, Job.StartDate, Job.[Expected duration], Job.EndDate
FROM Job
WHERE (((Job.EndDate) Between Date() And Date()-6));

Basically i am trying to show all projects that were running in the previous week from the date of running the query - thats all, but when i view the results i have things like september dates in amongst them - weird!?

I also need to do the same for a monthly report too.

I have a Startdate and an Enddate and need to display all jobs that were running last week and last month.
Also the Endate maybe unknown so can also be Is Null

Many thanks in advance

 

[pr2-eugin]

Hello Sam Summers, Have you checked if the problem you are facing is the common International Date problem in Access?

With regards to Null End Dates, should they be included in the search result?

 

[Sam Summers]

I will check that right now. Thank you
Yes, Null dates just for the end date as a job may be running but not ended or the end date will not be known.

 

[namliam]

Would you not want something like:
StartDate<= Date()
and
NZ(EndDate, Date() ) >= Date-6

 

[Sam Summers]

OK, Checked and changed that but i just cannot seem to get anywhere with the queries t represent the records which will be reports both weekly utilization and Monthly utilization of personnel.

This is what i have been juggling with with random results just when i thought i had got it right!
Any pointers in the right direction anyone before i jump out of the window (its only on the 1st floor!)

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Contract, Employee.RopeAccess, Employee.National, Employee.MobDate, Employee.DeMobDate, Job.Type
FROM Employee INNER JOIN (Job INNER JOIN EmpJob ON Job.JobID = EmpJob.JobID) ON Employee.EmployeeID = EmpJob.EmployeeID
WHERE (((Employee.Contract)=Yes) AND ((Employee.RopeAccess)=Yes) AND ((Employee.National)=Yes) AND ((Employee.MobDate) Between Date() And DateAdd("m",-1,Date()) And (Employee.MobDate) Is Not Null) AND ((Employee.DeMobDate) Between Date() And DateAdd("m",-1,Date())) AND ((Job.Type)="Mechanical Integrity")) OR (((Employee.Contract)=Yes) AND ((Employee.RopeAccess)=Yes) AND ((Employee.National)=Yes) AND ((Employee.MobDate) Between Date() And DateAdd("m",-1,Date()) And (Employee.MobDate) Is Not Null) AND ((Employee.DeMobDate) Is Null) AND ((Job.Type)="Mechanical Integrity"))
ORDER BY Employee.National;

 

[Sam Summers]

namliam, i tried that but i still get September and even August dates in both date fields?

Here is the SQL for this attempt:

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Contract, Employee.RopeAccess, Employee.National, Employee.MobDate, Employee.DeMobDate, Job.Type
FROM Employee INNER JOIN (Job INNER JOIN EmpJob ON Job.JobID = EmpJob.JobID) ON Employee.EmployeeID = EmpJob.EmployeeID
WHERE (((Employee.Contract)=Yes) AND ((Employee.RopeAccess)=Yes) AND ((Employee.National)=Yes) AND ((Employee.MobDate)<=Date() And (Employee.MobDate) Is Not Null) AND (NZ("EndDate",Date())>="Date-6") AND ((Job.Type)="Mechanical Integrity")) OR (((Employee.Contract)=Yes) AND ((Employee.RopeAccess)=Yes) AND ((Employee.National)=Yes))
ORDER BY Employee.National;

I must have made a mistake there somewhere? Maybe where the inverted commas are?

 

[namliam]

Sigh, why never anyone use the # sign or code wraps to post code...
Or even bother to make the code readable.... 2 obvious things can been seen when you make the SQL readable...:banghead:

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Contract, Employee.RopeAccess, Employee.National, Employee.MobDate, Employee.DeMobDate, Job.Type
FROM Employee 
INNER JOIN (Job 
INNER JOIN EmpJob ON Job.JobID = EmpJob.JobID) 
                  ON Employee.EmployeeID = EmpJob.EmployeeID
WHERE (     ((Employee.Contract  )=Yes) 
        AND ((Employee.RopeAccess)=Yes) 
        AND ((Employee.National  )=Yes) 
        AND (     (Employee.MobDate   )<=Date() 
              And (Employee.MobDate   ) Is Not Null
            ) 
        AND (NZ("EndDate",Date())>="Date-6") 
        AND ((Job.Type)="Mechanical Integrity")
      ) 
   [U][B]OR[/B][/U] (    ((Employee.Contract)=Yes) 
       AND ((Employee.RopeAccess)=Yes) 
       AND ((Employee.National)=Yes)
      )
ORDER BY Employee.National;

1) The OR part as bolded and underlined negates any limitation on date you put in the first part.
2) the NZ on endate is with " which is total nonsense and I hope is a simple mistake... But even if you had this right the OR in 1) would still cause you issues.

 

[Sam Summers]

I am sorry namliam, i didn't think about that but i will in future.

Thank you for your help, i will have a try and fix it