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
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