How do I count overlapping days in a single field of my database?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed
 
if you access to the code you can use '

like this

'' statement
''statement
''statement
''statement
 
Terry said:
I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed
Click to expand...

Try my DateIntersection function in a subquery. Something like:

SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;

'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------

This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.

James A. Fortune
(e-mail address removed)
 
I Tried this Coding but it gives me all kinds of Select case and end of
statement errors!

James A. Fortune said:
Terry said:
I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed
Click to expand...

Try my DateIntersection function in a subquery. Something like:

SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;

'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------

This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.

James A. Fortune
(e-mail address removed)
Click to expand...
 
Terry said:
I Tried this Coding but it gives me all kinds of Select case and end of
statement errors!

:

Terry said:
I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed
Click to expand...

Try my DateIntersection function in a subquery. Something like:

SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;

'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------

This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.

James A. Fortune
(e-mail address removed)
Click to expand...
Click to expand...

Terry,

The part before 'Begin module code ---- is meant to be the SQL for a
query rather than code inside a module or behind a form. When you're in
the Query Designer look for the SQL button.

James A. Fortune
(e-mail address removed)
 
I still get Extra ) in query expression

James A. Fortune said:
Terry said:
I Tried this Coding but it gives me all kinds of Select case and end of
statement errors!

:

Terry wrote:

I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed

Try my DateIntersection function in a subquery. Something like:

SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;

'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------

This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.

James A. Fortune
(e-mail address removed)
Click to expand...
Click to expand...

Terry,

The part before 'Begin module code ---- is meant to be the SQL for a
query rather than code inside a module or behind a form. When you're in
the Query Designer look for the SQL button.

James A. Fortune
(e-mail address removed)
Click to expand...
 
Terry said:
I still get Extra ) in query expression

:

Terry said:
I Tried this Coding but it gives me all kinds of Select case and end of
statement errors!

:



Terry wrote:


I am trying to count overlapping dates from a single field of my program. I
have a field which has the same stock number in it. Then I have a field that
has the dates it was gone. I am trying to figure out how to count the dates
that are overlapping so I get a count of the stock number that are gone at
the same time. The stock number field and the date fields are set up in the
following format:

Stock Number Date out Date in
000000000 12Jan06 15Feb06
000000000 20Jan06 09Feb06

There are about 5000 entries in the database.

Any help would be greatly apprecitaed

Try my DateIntersection function in a subquery. Something like:

SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;

'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------

This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.

James A. Fortune
(e-mail address removed)
Click to expand...

Terry,

The part before 'Begin module code ---- is meant to be the SQL for a
query rather than code inside a module or behind a form. When you're in
the Query Designer look for the SQL button.

James A. Fortune
(e-mail address removed)
Click to expand...
Click to expand...

Ah... I see the problem now. It is "Something like:" :-).

I didn't paste the SQL from a working query. It was off the top of my
head. That's why the parentheses didn't match up. What was I thinking?
Try changing "WHERE DateIntersection..." to "WHERE (DateIntersection..."

I can't guarantee that will work either, but it's my best guess since I
don't know if " > 0 AND..." will try some weird binary operation that
will always come out False without looking up operator precedence in the
Help File.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to structure a Table with many, many, many Fields? 16
How can I count dates that overlap each other 2
Count of distinct records for each group header 9
stacked fields 'can grow' but overlap in report 2
Counting Working Days 4
Calc Sales based on Stock Count 3
calculated fields in a report 4
COUNT TYPES OF ITEMS IN A FIELD 1

Back
Top