Calculations between records based on Dates?

[sberti]

Hi,
Is what I want to do possible? I haven't found a solution. I mainly use just Access. I'm not good a VBA.

I have a table that has a Date field and a field called "InOut".

Example
2015-10-14 In
2016-01-01 Out


2016-02-01 In
2016-03-15 Out
2016-04-20 In

The dates are random, not at the beginning or end of a month.

In this example the item was "In":
Between 14-Oct-15 and 01-Jan-16
Then again between 01-Feb-16 and 15-Mar-16
And from 20-Apr-16 until Now()

Is there a way calculate the total "In" days in a query or series of queries?

[CJ_London]

something like?

SELECT count(*)
FROM myTable
WHERE InOut="In"

[orange]

How do you track multiple items?

[CJ_London]

on rereading, I misunderstood the question. instead try

SELECT sum(datadiff("d",[myDate],nz((SELECT min(myDate) FROM myTable as T WHERE mydate>myTable.myDate AND inOut='Out'),Date())) as daysIn
FROM myTable
WHERE inOut='In'

you may need to swap [myDate] and the nz function round if you get a negative number

[sberti]

on rereading, I misunderstood the question. instead try

SELECT sum(datadiff("d",[myDate],nz((SELECT min(myDate) FROM myTable as T WHERE mydate>myTable.myDate AND inOut='Out'),Date())) as daysIn
FROM myTable
WHERE inOut='In'

you may need to swap [myDate] and the nz function round if you get a negative number

Sorry, I think I didn't explain correctly.

I need to count the number of days in the time periods from each In Date to the next Out Date.
For every In Date there will be an Out Date, except for the last In Date which will need to calculate up to today.

14-Oct-15 to 01-Jan-16 = 78 days
01Feb-16 to 15-Mar-16 = 43 days
20-Apr-16 to today (06-Jul-16) = 77 days
Total "In" Days = 198

I'll try your suggestion. Thanks

[sberti]

on rereading, I misunderstood the question. instead try

SELECT sum(datadiff("d",[myDate],nz((SELECT min(myDate) FROM myTable as T WHERE mydate>myTable.myDate AND inOut='Out'),Date())) as daysIn
FROM myTable
WHERE inOut='In'

you may need to swap [myDate] and the nz function round if you get a negative number

I've created a test table called myTable with fields called myDate and inOut, so I could try your exact code.

But I keep getting a Syntax error message: Missing operator in the query expression.

When I close the error message, the word "as" before the words "daysIn FROM myTable" is highlighted. I've tried a few things, but can't figure out what's wrong.
Any suggestions?

[CJ_London]

since names of tables and fields had not been provided and unless I spend the time creating a table to base the query on the code I provided is pseudo code., it is quite possible that I have made a spelling mistake, or missed a bracket or included one too many. The error message implies a problem with brackets - so do something similar to below in word or some other text editor - even on this forum to see that the brackets match up

sum(datadiff("d",[myDate],nz((SELECT min(myDate) FROM myTable as T WHERE mydate>myTable.myDate AND inOut='Out'),Date()))

and hey presto, there is not a matching one for the sum function. So add another at the end and that should solve your problem.

[sberti]

I've added the bracket and I'm not getting any errors.
But the result is not the correct number.

Thanks for your help. I'll keep working on it.

[sberti]

since names of tables and fields had not been provided and unless I spend the time creating a table to base the query on the code I provided is pseudo code., it is quite possible that I have made a spelling mistake, or missed a bracket or included one too many. The error message implies a problem with brackets - so do something similar to below in word or some other text editor - even on this forum to see that the brackets match up

sum(datadiff("d",[myDate],nz((SELECT min(myDate) FROM myTable as T WHERE mydate>myTable.myDate AND inOut='Out'),Date()))

and hey presto, there is not a matching one for the sum function. So add another at the end and that should solve your problem.

It wasn't giving me the correct numbers but I've been messing with it and finally removed the Sum() function and it brings up the correct individual numbers of days. This is great! I can do the Sum() in a separate query.

Thank you! Thank you! Thank you!

[CJ_London]

no problem