Counting dates

[Sam Summers]

Hi there,

Trying to get my head round this one?

I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?

I have two fields - MobDate and DeMobDate

I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.

I know for someone it will be simple?

Many thanks in advance

 

[CJ_London]

have you tried using the datediff function?

datediff("d",demobdate,mobdate)

 

[Sam Summers]

I was thinking along that line but the MobDate and DeMobDate could be ongoing?
Will it work for example if the DeMobDate is blank and the MobDate was six months prior when all i want is to display the number of days in the past week that the equipment has been on hire?

 

[pr2-eugin]

Can you give a few examples !

 

[Sam Summers]

Ok,
Example 1:

MobDate = 29/03/14
DeMobDate = Null (because the equipment is still on hire)

I want to display in a TextBox (NOD) the number of days in the previous week from todays date that the equipment has been on hire.

Example 2:

MobDate = 22/05/14
DeMobDate = Null (because the equipment is still on hire)

I want to display in a TextBox (NOD) the number of days in the previous week from todays date that the equipment has been on hire.

Example 3:

MobDate = 29/03/14
DeMobDate = 25/05/14

I want to display in a TextBox (NOD) the number of days in the previous week from todays date that the equipment has been on hire.

 

[CJ_London]

So you need to calculate the start and end of last week as well?

Can you define when the week starts? Sunday? Monday? Or by last week do you mean the last 7 days from today?

Are weekends included as well - so the maximum NOD would be 7?

 

[Sam Summers]

Yes, the number of days will be within or all of the last 7 days from today and as you said CJ weekends are included

 

[CJ_London]

You'll need something like this

=Dlookup("Nz([demobdate],Date()-1)-IIf([mobdate]>Date()-7,[mobdate],Date()-7)","myTable","mobdate<=Date()-1 AND (demobdate Is Null Or demobdate>=Date()-7) AND id=" & me.ID)

You'll need to change the table name and ID for the actual names you are using

 

[Sam Summers]

Thanks CJ,
I have been trying to make this work but i am getting #Error
I am not 100% sure of certain bitys of this as this is beyond my current knowledge?
This is what i have at the moment which is not working:

SELECT Employee.JobID, Employee.FirstName, Employee.Surname, Employee.JobTitle,Employee.MobDate, Employee.DeMobDate, DLookUp("Nz([DeMobDate],Date()-1)-IIf([MobDate]>Date()-7,[MobDate],Date()-7)","Employee","MobDate<=Date()-1 AND (DeMobDate Is Null Or DeMobDate>=Date()-7) AND id=" & [Employee].[EmployeeID]) AS Expr1
FROM Employee;

I'm not sure about the id part?

 

[CJ_London]

Looks like you've missed a correction - id should be EmployeeID

DLookUp("Nz([DeMobDate],Date()-1)-IIf([MobDate]>Date()-7,[MobDate],Date()-7)","Employee","MobDate<=Date()-1 AND (DeMobDate Is Null Or DeMobDate>=Date()-7) AND id=" & [Employee].[EmployeeID])

Also, just realised I assumed from your posts that you wanted to populate a control on a form, rather than in a query so since DLookup is quite slow you may be better using a subquery like this

SELECT Employee.JobID, Employee.FirstName, Employee.Surname, Employee.JobTitle,Employee.MobDate, Employee.DeMobDate, (SELECT First(Nz([DeMobDate],Date()-1)-IIf([MobDate]>Date()-7,[MobDate],Date()-7)) FROM Employee AS Tmp WHERE MobDate<=Date()-1 AND (DeMobDate Is Null Or DeMobDate>=Date()-7) AND EmployeeID=[Employee].[EmployeeID]) AS Last7Days
FROM Employee;

 

[Sam Summers]

Hi again,

Well, both work but they are showing all totals? i.e. 23 days, 17 days etc. ?

 

[Sam Summers]

Just thinking too, if there is a way of counting the number of days on hire between todays date and 7 days prior?

Just ideas of how it has to function?

 

[CJ_London]

they are showing all totals

Is employeeID unique in the table or can it appear more than once? If it can then try

SELECT Employee.JobID, Employee.FirstName, Employee.Surname, Employee.JobTitle,Employee.MobDate, Employee.DeMobDate, Nz([DeMobDate],Date()-1)-IIf([MobDate]>Date()-7,[MobDate],Date()-7) AS Last7Days
FROM Employee

i

f there is a way of counting the number of days on hire between todays date and 7 days prior?

That is basically what the query does. If you want to count up to and including today rather than yesterday then remove the -1 from Date()-1 and change the 7 to a 6.

Note the above assumes that your two date fields are just that and do not include a time element.

 

[Sam Summers]

Its strange and i am trying to see why its producing these figures?

These dates are displaying a MobDate of 15-05-14 and a DeMobDate of 13-06-14 and the number of days despite the query is displaying as 23?

I just want to add up the days of hire within the last 7 days?

Interesting?

 

[Sam Summers]

Will something like this work?

=DateDiff("d",Date()-7 And Date()) If MobDate<=Date() And DeMobDate>= Date() Or MobDate<=Date() And DeMobDate = Null

 

[CJ_London]

try it and see!

try breaking the calculation down into its various components and see what the values are

[COLOR=magenta]Nz([DeMobDate],Date()-1)[/COLOR]-[COLOR=darkorange]IIf([MobDate]>Date()-7,[MobDate],Date()-7) [/COLOR]AS Last7Days

Nz([DeMobDate],Date()-1) should show the dmobdate or yesterday if dmobdate is null

IIf([MobDate]>Date()-7,[MobDate],Date()-7) should show the mobdate if it is in the last week, otherwise it shows the date 7 days ago

Which explains part of the problem - if the dmobdate is older than a week ago it shows the dmobdate, but you need this as a condition - if the dmobdate is older than a week ago you need to return 0 so the formula needs to be revised to

[COLOR=magenta][COLOR=black]iif(nz(demobdate,Date()-1)<(Date()-7),0,[/COLOR]Nz([DeMobDate],Date()-1)[/COLOR]-[COLOR=darkorange]IIf([MobDate]>Date()-7,[MobDate],Date()-7)[COLOR=black])[/COLOR] [/COLOR]AS Last7Days

 

[sumox]

Can you give a few examples !

Dear sir pr2-eugin,

Pls see my posts related to VBA in Newposts section


Pls thanks in advance pls. solve

Sirs's pleeeease solve it !

Many thanks ia

 

[pr2-eugin]

Dear sir pr2-eugin,

Pls see my posts related to VBA in Newposts section


Pls thanks in advance pls. solve

Sirs's pleeeease solve it !

Many thanks ia

Sorry to say this, but I have no clue what you are talking about.

 

[Sam Summers]

Thanks guys,
I know there is always so much on this subject.
I will try that right now CJ.

I had a different idea regarding this and posted this:

http://www.access-programmers.co.uk/forums/showthread.php?p=1352844#post1352844

But it is not working either?

 

[Sam Summers]

OK, I tried that CJ - still not right?

Your previous example worked but the main issue is that there seems to be no upper limit?
In other words if the DeMobDate is greater than Date() it returns the number of days up until the actual DeMobDate i.e. 23

I think my other post explains more clearly what i am trying to do?