compare dates in MS access

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Can anyone tell me how I can compare date in MS Access?

here's what I trying to do..

+--------------+-------------|
| Start Date | End Date |
| 22/7/2005 | 7/9/2005 |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
| ... | ... |

Critera: month of April exist between [Start Date] & [End Date]

output...
+--------------+-------------|
| Start Date | End Date |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
|----------------------------- |

Thank you so much.. ^^
 
Hi Ben,

Do you mean you want records where either the StartDate or the EndDate
is in between 1 April 2005 and 30 April 2005, or records where either
data is in the month of April, or something else??

If the first, and assuming these are date fields, try something like

SELECT * FROM MyTable
WHERE (StartDate BETWEEN #2005/04/01# AND #2005/04/30#)
OR (EndDate BETWEEN #2005/04/01# AND #2005/04/30#)

Note that when you're using date literals in SQL you mustn't use the
usual dd/mm/yyyy format; SQL will try to interpret it as mm/dd/yyyy, so
1/4/2005 will be read as 4 January, not 1 April. Using yyyy/mm/dd avoids
this source of problems.

Can anyone tell me how I can compare date in MS Access?

here's what I trying to do..

+--------------+-------------|
| Start Date | End Date |
| 22/7/2005 | 7/9/2005 |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
| ... | ... |

Critera: month of April exist between [Start Date] & [End Date]

output...
+--------------+-------------|
| Start Date | End Date |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
|----------------------------- |

Thank you so much.. ^^
Click to expand...
 
Ben said:
Can anyone tell me how I can compare date in MS Access?

here's what I trying to do..

+--------------+-------------|
| Start Date | End Date |
| 22/7/2005 | 7/9/2005 |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
| ... | ... |

Critera: month of April exist between [Start Date] & [End Date]

output...
+--------------+-------------|
| Start Date | End Date |
| 28/2/2005 | 28/4/2005 |
| 1/4/2005 | 30/6/2005 |
|----------------------------- |

Thank you so much.. ^^
Click to expand...

Public Function AprilDateIntersection(dt3 As Date, dt4 As Date) As Integer
Dim intSumDateIntersection As Integer
Dim dt1 As Date
Dim dt2 As Date
Dim dtTemp As Date

'Return the number of days of any April overlapping a date range
'Assumes d3 <= d4
intSumDateIntersection = 0

'Loop for all Aprils between years of dt3 and dt4
dtTemp = dt3
Do While Year(dtTemp) <= Year(dt4)
dt1 = DateSerial(Year(dtTemp), 4, 1)
dt2 = DateSerial(Year(dtTemp), 4, 30)
If dt2 <= dt3 Then
If dt2 = dt3 Then intSumDateIntersection = intSumDateIntersection + 1
ElseIf dt4 <= dt1 Then
If dt4 = dt1 Then intSumDateIntersection = intSumDateIntersection + 1
ElseIf dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
intSumDateIntersection = intSumDateIntersection + DateDiff("d",
dt3, dt2) + 1
ElseIf dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
intSumDateIntersection = intSumDateIntersection + DateDiff("d",
dt3, dt4) + 1
ElseIf dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
intSumDateIntersection = intSumDateIntersection + DateDiff("d",
dt1, dt2) + 1
ElseIf dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
intSumDateIntersection = intSumDateIntersection + DateDiff("d",
dt1, dt4) + 1
End If
dtTemp = DateAdd("yyyy", 1, dtTemp)
Loop
AprilDateIntersection = intSumDateIntersection
End Function

I only tried one date range (with U.S. date format) so let me know your
test results if you try it.

MsgBox (AprilDateIntersection(#3/1/2006#, #4/3/2006#)) => 3

For your situation try:

WHERE AprilDateIntersection([Start Date], [End Date]) > 0

Post back if you need to know how many complete Aprils are in the date
range.

James A. Fortune
(e-mail address removed)
 
Back
Top