Calculate Date Period ?

[amb3rsil]

Hi,

Im having some issues getting my head round a date problem. Im building an animal sightings database and am trying to calculate the age of an animal when it was sighted, which is calculated based on their DOB. It is not as simple as comparing the animals dob and the date of the sighting, returning an age say of 4 years 9 months or whatever, but needs to be in terms of seasons i.e winter and summer and the amount of those it has had. To explain :-

A winter season is between 16 August and 15 february every year.

A summer season is between 16 February and 15 august every year.

So say for example an animal was sighted this month october 2003 and it was actually born a year ago it has already had 1 winter season (WINTER1) and 1 summer season (SUMMER1) so in fact its age would now be WINTER2 (seeing as the date of the sighting today the 12th of october is well into the winter season). When febuary 15 comes round next year and we change season to summer and it gets sighted then, its age at that sighting will then become SUMMER2 and then this time next year it will be WINTER3 and so on and so on.

Confused? I know, its kinda tricky to explain but read the above para a couple of times (its one of THOSE problems

NOTE an animal does not have to have completed a whole season for it to count, so for example if an animal was born today and sighted tommorow, its age would still be WINTER1.

This value WINTER1,SUMMER4 or whatever needs then just to stuck in a variable so I can then stick it on reports queries and forms.

Im not sure how to do this, I thought about counting day periods for each season..but then leap years will cause problems. I dont particualarly want to create a table with winter1 summer1 winter2 summer2 winter3 summer3 and their corresponding date period etc etc. As that would be a very clumsy way of doing it.

I would also ideally like to "shift the goalposts" in that I could easily create a form which an administrator of the db could adjust the date periods for summer and winter.

ANY help with this matter would be wonderful, its for science too, so you can feel good that your helping animal research

Cheers,

Amb3rsil

 

[VinceL]

Dear Amb3rsil:

Please try the following code. This may not be the most efficient way, but it seems to work. My approach was by adjusting the year so that a winter would not be spit to cover two years. The code assigns a season to the DOB and DOS (sighting date) and then the season year (1, 2, etc.) depending on the situation.

Hope this helps.

VinceL

Private Sub cmdAge_Click()
' You'll need input boxes called "DOB_" and "DOS_" in a form and corresponding linked fields from a table

Dim BirthSeason As Integer ' Summer = 1; Winter = 2
Dim SightSeason As Integer ' Summer = 1; Winter = 2
Dim CorYearB As Integer 'Corrected year for birth Eg. January 2003 = 2002; Dec 2002 = 2002
Dim CorYearS As Integer 'Corrected year for sighting
Dim YearDiff As Integer 'Difference in
Dim SightAge As String 'Age at Sighting
CorYearB = Year(DOB_)
CorYearS = Year(DOS_)
Select Case Month(DOB_)
Case 1
BirthSeason = 2
CorYearB = Year(DOB_) - 1 'Adjusted to the previous year
Case 2
If Day(DOB_) > 15 Then
BirthSeason = 1
Else
BirthSeason = 2
CorYearB = Year(DOB_) - 1 'Adjusted to the previous year
End If
Case 3, 4, 5, 6, 7
BirthSeason = 1
Case 8
If Day(DOB_) > 15 Then
BirthSeason = 2
Else
BirthSeason = 1
End If
Case 9, 10, 11, 12
BirthSeason = 2
Case Else
End Select

Select Case Month(DOS_)
Case 1
SightSeason = 2
CorYearS = Year(DOS_) - 1
Case 2
If Day(DOS_) > 15 Then
SightSeason = 1

Else
SightSeason = 2
CorYearS = Year(DOS_) - 1
End If
Case 3, 4, 5, 6, 7
SightSeason = 1
Case 8
If Day(DOS_) > 15 Then
SightSeason = 2

Else
SightSeason = 1
End If
Case 9, 10, 11, 12
SightSeason = 2
Case Else
End Select

YearDiff = CorYearS - CorYearB

If BirthSeason = SightSeason Then
YearDiff = YearDiff + 1
Else
If SightSeason > BirthSeason Then
YearDiff = YearDiff + 1
End If
End If
Select Case SightSeason
Case 1
SightAge = "Summer" & YearDiff
Case 2
SightAge = "Winter" & YearDiff
Case Else

End Select


MsgBox "Age = " & SightAge
'You may add this to another input box or field etc if you so desire.

End Sub