Table calculations. Season from Date in Hunting Harvest Database.

[Tim]

I am trying to set up a hunting harvest database. I would like to add a
column to the original data table that automatically calculates the hunting
season, which is based on a specific harvest timeframe. Each harvest record
entry has a specific date. For example, when a record of a deer harvested
on 12-3-2007 is entered; I want the database to calculate that the date
occurred during the fall 2007 season. Thus, I want 2007 to be automatically
entered for this record in a separate column. However, our seasons last into
the new year, so if for example a record is entered of a deer harvested on
1-23-2008, I want the database to calculate that the date still occurred
during the fall 2007 season.

 

[John Spencer]

Best method would be to set up a table with three columns
tblHuntingSeasons
==fldStartDate
==fldEndDate
==fldSeasonName

Then when you need to hunting season you can either use DLookup function or
better join the hunting season table to your hunting harvest table.

DLookup would look like the following as a calculated field in your query
Field: Season: DLookup("fldSeasonName",
"tblHuntingSeason",Format([HarvestDateField], "\#yyyy-mm-dd\#") & " Between
fldStartDate and fldEndDate")

The subquery would look like:

Field: Season: (SELECT First(fldSeasonName) FROM tblHuntingSeasons WHERE
[YourTable].[HarvestDate] Between fldStartDate and fldEndDate)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County