The logical design for this would be to have a table Members with a primary
key column MemberID and a table Addresses with a foreign key column MemberID
referencing the primary key of Members. I'd suggest that to cater for the
varying departure dates of members you have columns of integer number data
type, MonthFrom, DayFrom, MonthTo and DayTo, in the Addresses table so if a
member departs for their winter address on 23 October and returns on 6 April
say the values in these four columns would be 10, 23, 4, 5 for the row in the
table with their winter address and 4,6, 10, 22 for their summer address.
There is no reason of course why someone can't have more than two addresses
so long as the dates don't overlap.
To determine which address is the right one for the current date a simple
function which returns True or False for the dates in each row would be an
appropriate way to go. The following is adapted from some similar code of my
own to suit your scenario and should do the trick, I think. Just paste the
function into a standard module in your database:
Function IsCurrentAddress(intMonthFrom As Integer, _
intDayFrom As Integer, _
intMonthTo As Integer, _
intDayTo As Integer, _
Optional varCurrentDate As Variant) As Boolean
Dim dtmDateFrom As Date, dtmDateTo As Date
Dim intMonthDayFrom As Integer, intMonthDayTo As Integer
Dim intMonthDayCurrentDate As Integer
Dim intYearFrom As Integer, intYearTo As Integer
If IsMissing(varCurrentDate) Then varCurrentDate = VBA.Date
' get dates as integers in format mmdd
intMonthDayFrom = Format(intMonthFrom, "00") & _
Format(intDayFrom, "00")
intMonthDayTo = Format(intMonthTo, "00") & _
Format(intDayTo, "00")
intMonthDayCurrentDate = Format(varCurrentDate, "mmdd")
If intMonthDayFrom < intMonthDayTo Then
' date range starts and finishes in current year
intYearFrom = Year(varCurrentDate)
intYearTo = Year(varCurrentDate)
Else
' date range spans end of year
If intMonthDayCurrentDate <= intMonthDayTo Then
' date range starts in previous year and
' finishes in current year
intYearTo = Year(varCurrentDate)
intYearFrom = intYearTo - 1
Else
' date range starts in current year and
' finishes in next year
intYearFrom = Year(varCurrentDate)
intYearTo = intYearFrom + 1
End If
End If
dtmDateFrom = DateSerial(intYearFrom, intMonthFrom, intDayFrom)
dtmDateTo = DateSerial(intYearTo, intMonthTo, intDayTo)
IsCurrentAddress = (varCurrentDate >= dtmDateFrom And _
varCurrentDate <= dtmDateTo)
End Function
The function takes the four month/day values as its arguments, plus an
optional date value which defaults to the current date. So to return a set
of addresses for the current date you'd cal it in a query like so:
SELECT FirstName, LastName, Address
FROM Members INNER JOIN Addresses
ON Members.MemberID = Addresses.MemberID
WHERE IsCurrentAddress(MonthFrom, DayFrom, MonthTo, DayTo);
If you wanted to specify a day you'd just add the date as an additional
argument, so to list everyone's addresses on Christmas day this year:
SELECT FirstName, LastName, Address
FROM Members INNER JOIN Addresses
ON Members.MemberID = Addresses.MemberID
WHERE IsCurrentAddress(MonthFrom, DayFrom, MonthTo, DayTo, #12/25/2005#);
or you could reference a text box control on a dialogue form as a parameter
(giving the text box a DefaultValue property of Date() so it opens with the
current date in place) and open a form or report based on the query from a
button on the form:
PARAMETERS Forms!YourForm!txtDate DATETIME;
SELECT FirstName, LastName, Address
FROM Members INNER JOIN Addresses
ON Members.MemberID = Addresses.MemberID
WHERE IsCurrentAddress(MonthFrom, DayFrom, MonthTo, DayTo,
Forms!YourForm!txtDate);
