Finding records between dates 1

[cashe]

Hi all,

I have a table with [Mobilised Date] and [Demobilised Date]. In a query I need to find all consultants that were mobilised on a project between two dates. For example Between 1.10.02 And 31.10.02. I can do this by either using the Mob or demob field however this only shows consultants that were actualy mobilised or demobilised. I need to show consultants working on a project and these consultants could have been mobilised a year ago. How do I do this?

Example:
Consultant X was mobilised on 12.01.01 and will be demobilised on the 1.10.04. Consultant X is working on project B. If I run a query to find mobilised consultants on Project B Between 1.10.02 And 31.10.02 I need consultant X to be displayed in the query because he is still working on the project.

Do I do this by system Date? The query will be used for reports as a Parameter query so users can select dates.

Hope there is help out there because I'm going nuts ;-)

Cashe

 

[PaulBricker]

You could try something like this. In a new column of your query put this expression

Status:IIf(Date() >= [MobilisedDate] And Date() <=[DemobilisedDate},&quot;Active&quot;,&quot;Deactive&quot

Then query by the Between ...And dates and Status = Active

That should return the records you need.

Paul

 

[Sam92Legacy]

I think the simplest way to deal with this is to divide the possible results into three different groups:
1. Consultants mobilized before October and demobilized after October
2. Consultants mobilized in October
3. Consultants demobilized in October

To catch all of these records, you will need to use three criteria rows. In the first row, set Mob to <=#10/1/02# and Demob to >=#10/31/02#. In the second row, set Mob to >=#10/1/02# And <=#10/31/02# and leave Demob blank. In the third row, leave Mob blank and set Demob to >=#10/1/02# And <=#10/31/02#. (I'm using the US English syntax for dates, so adjust it to your localization.)

I think this should do it.

Sam

 

[r937]

paul, date() wouldn't come into consideration

there are two pairs of dates here: the mod/demob date columns in the table, which vary for each consultant, and the pair of dates that define the search range, let's call them startdate and enddate, for which cashe gave the examples 1.10.02 and 31.10.02

paul's three conditions would be combined with OR conditions:

 where mob < startdate and demob > enddate
    or mob between startdate and enddate
    or demob between startdate and enddate

rudy

 

[PaulBricker]

rudy, if I had written it out like you did I would have seen it a lot clearer. You are correct. Today's date doesn't do anything. Something like this might

Status:IIf([mob]<= [EnterStartDate] and [Demob]>= [EnterEndDate],&quot;Active&quot;,&quot;Deactive&quot

Then you could query for ProjectNumber and Status = &quot;Active&quot;

Paul

 

[r937]

thanks paul

it still needs 3 tests, though

for example, your Status does not detect a consultant whose mod or demob date is within the range

 

[PaulBricker]

Or we could do it Sam's way and just change the actual dates for parameter boxes. The parameters should only run once as long as they are worded exactly the same.

Paul

 

[cashe]

Sam, Paul and Rudy,

Thanks very much for your help. Sorry I did not reply over the weekend. I'm back in the office today so will try Sam's method. I'll keep you posted with feedback.

Thanks

Cashe

 

[cashe]

Hi guys,

Ok. It works using Sam's method. Thanks Sam!! I've pasted below what it looks like in my X tab query grid:

Mobilised Date
tblAssigned
Where

<=[Start Date]
>=[Start Date] And <=[End Date]

Demobilised Date
tblAssigned
Where

>=[End Date]
>=[Start Date] And <=[End Date]


The parameters work well and it produces the right mobilised consultants working on the projects. However one small problem which I'm trying to fix. I have another parameter field for location. when the user runs the query, it asks for Location, Start Date, End Date. The location parameter does not work now. All locations are visible if they fit the Start and End date criteria. Any suggestions? I've pasted the SQL below just for interest.

Thanks again

Cashe ;-)

PARAMETERS [Enter Consultant Location] Text ( 255 ), [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Nz((Count([tblAssigned].[Type of Employee])),0) AS [CountOfType of Employee]
SELECT tblAssigned.[Project name], tblContractor.Discipline, tblAssigned.Location, Sum([CountOfType of Employee]) AS [Sum]
FROM tblContractor INNER JOIN tblAssigned ON tblContractor.[Contractor ID] = tblAssigned.[Contractor ID]
WHERE (((tblContractor.Discipline) Like &quot;ELECTRI*&quot; Or (tblContractor.Discipline)=&quot;PIPING&quot; Or (tblContractor.Discipline)=&quot;PROCESS&quot; Or (tblContractor.Discipline)=&quot;SAFETY&quot; Or (tblContractor.Discipline)=&quot;MECHANICLE&quot; Or (tblContractor.Discipline)=&quot;STRUCTURAL&quot; Or (tblContractor.Discipline)=&quot;INSTRUMENTATION&quot AND ((tblAssigned.Location) Like [Enter Consultant Location] & &quot;*&quot AND ((tblAssigned.[Mobilised Date])<=[Start Date]) AND ((tblAssigned.[Demobilised Date])>=[End Date])) OR (((tblAssigned.[Mobilised Date])>=[Start Date] And (tblAssigned.[Mobilised Date])<=[End Date]) AND ((tblAssigned.[Demobilised Date])>=[Start Date] And (tblAssigned.[Demobilised Date])<=[End Date]))
GROUP BY tblAssigned.[Project name], tblContractor.Discipline, tblAssigned.Location
PIVOT tblAssigned.[Type of Employee] In (&quot;Employed&quot;,&quot;Hired&quot;

 

[cashe]

Just incase you guys look at this again.

Everything is sorted. Thanks for your help.

Cashe