Need help on MM/DD query; nothing I've tried gives me an end to end solution --Help please!
Bill
AnnivDate, for a wedding anniversary. AnnivDate has nulls and some
incorrect year data. I have been creating the Access database using
data imported from an Excel file and the person entering into Excel
only cared about the month and day portion and Excel added the current
year to the field value. I want to produce a report that shows
upcoming anniversaries using a parameter dialog form and two unbound
controls for a "From" date and a "To" date that are usually one
or two weeks apart. Also in the dialog form I'd like to set defaults
for each date to show the From date as the next Sunday and the To date
as the following Saturday. I have the report and the parameter dialog
form designed and functioning but I haven't been able to produce
correct data on the report. I'm using a SELECT query as data source
for both report and parameter form. I have the usual name, city, state
fields in the query as well as the AnnivDate field. I have been
reading books and internet searching for help and have added the
following to the query: AnnivNorm:
DateSerial(Year(Date()),DatePart('m',[AnnivDate]),DatePart('d',[AnnivDate]))
which gives me the correct month and day and normalizes everything to
the current year; then I have AnnivSort:
Format([AnnivNorm],"yyyymmdd") which gives me an 8 digit number;
then I have two columns which produce the month and day numerical
values AnnivM: DatePart('m',[AnnivDate]) and same for day; then I
have a column which produces the month and day Date:
Format$([AnnivDate], "mm/dd"); and lastly I have two columns that
hold the "From" and "To" dates given in the parameter form so I
can have them print on the final report. I can sort ascending on Date
a get an ordered list and I can manually set parameters (Between AND)
on Date (format mm/dd) and get a correct subset except when "To" is
in the next year from "From". When I try to put all the pieces
together I either get everybody or nobody or a subset that includes Nov
and Dec dates in a Jan window.
I'm learning MSAccess by self-teaching and I've been able to
create a report that opens a parameter form with unbound date controls
to pass parameters to a query and correctly select records between the
two dates but it has always been using a full date. I'm really
stumped on getting this Month Day setup to work. I've found good
stuff on the web and tried multiple combinations but haven't got it
to work. I need an end to end solution to be able to select people who
have anniversaries between any two dates (sequential of course)
including a Dec "From" to a Jan "To" and be able to handle
nulls.
The SQL below seems to get me a good report but not for the Dec to
Jan problem. The unbounds in the parameter form (fdlgAnnivDateSelect)
do not have a format specified but do have an input mask of 99/99;0;_.
If I put a short date format there the results are wrong.
SELECT tblMembersYrComb.IDMem, tblMembersYrComb.LastName,
tblMembersYrComb.FirstNameHusb, tblMembersYrComb.FirstNameWife,
tblMembersYrComb.AnnivDate, tblMembersYrComb.City,
tblMembersYrComb.State,
DateSerial(Year(Date()),DatePart('m',[AnnivDate]),DatePart('d',[AnnivDate]))
AS AnnivNorm, Format([AnnivNorm],"yyyymmdd") AS AnnivSort,
DatePart('m',[AnnivDate]) AS AnnivM, DatePart('d',[AnnivDate]) AS
AnnivD, Format$([AnnivDate],"mm/dd") AS [Date],
Forms!fdlgAnnivDateSelect!txtFrom AS [From],
Forms!fdlgAnnivDateSelect!txtTo AS [To]
FROM tblMembersYrComb
WHERE (((tblMembersYrComb.AnnivDate)=True) AND
((Format$([AnnivDate],"mm/dd")) Between
[Forms]![fdlgAnnivDateSelect]![txtFrom] And
[Forms]![fdlgAnnivDateSelect]![txtTo]))
ORDER BY
DateSerial(Year(Date()),DatePart('m',[AnnivDate]),DatePart('d',[AnnivDate]));
I'd really appreciate any help from the experts. Thanks much!
Bill
CDMAP...@fortunejames.com
In:
http://groups.google.com/group/comp.databases.ms-access/msg/4b1f69113a7d219e
I wrote:
Public Function boolBirthdayThisWorkWeek(dtBirth As Date, dtTimeTicket
As Date) As Boolean
Dim dtSundayStarting As Date
Dim dtSaturdayEnding As Date
Dim intAgeSaturday As Integer
Dim intAgeSunday As Integer
dtSundayStarting = DateAdd("d", 1 - WeekDay(dtTimeTicket),
dtTimeTicket)
dtSaturdayEnding = DateAdd("d", 6, dtSundayStarting)
intAgeSaturday = Int(Format(dtSaturdayEnding, "yyyy.mmdd") -
Format(dtBirth, "yyyy.mmdd"))
intAgeSunday = Int(Format(dtSundayStarting, "yyyy.mmdd") -
Format(dtBirth, "yyyy.mmdd"))
boolBirthdayThisWorkWeek = (intAgeSunday <> intAgeSaturday) Or
(Format(dtSundayStarting, "\.mmdd") = Format(dtBirth, "\.mmdd"))
End Function
You should be able to use that idea for anniversaries. Note that the
"\.mmdd" part matches only the month and day part of the dates. The
logic is that for a given time interval the number of years since the
anniversary can only increment if the month and day of the anniversary
fall within that range. Checking the years since the anniversary for
the first and last days of the interval handle every day of the
interval except for the first day of the interval which must be checked
separately. This technique should work across year boundaries. It
should also work if just the month and day of the anniversary are given
but an arbitrary year, say 2006, might be better to use in cases where
the actual year of the anniversary is unknown.
James A. Fortune
CDMAP...@FortuneJames.com
CDMAP...@fortunejames.com
> You should be able to use that idea for anniversaries. Note that the
> "\.mmdd" part matches only the month and day part of the dates. The
> logic is that for a given time interval the number of years since the
> anniversary can only increment if the month and day of the anniversary
> fall within that range. Checking the years since the anniversary for
> the first and last days of the interval handle every day of the
> interval except for the first day of the interval which must be checked
> separately. This technique should work across year boundaries. It
> should also work if just the month and day of the anniversary are given
> but an arbitrary year, say 2006, might be better to use in cases where
> the actual year of the anniversary is unknown.
I think this can be simplified by using the date before the initial day
of the interval. Then you only need to see if that anniversary age is
different than the anniversary age for the last day of the interval.
Maybe use IIF(IsNull(varAnniversary), False, <expression>) with
varAnniversary as a Variant to set the function False when the
anniversary date is Null. Something like:
Public Function boolAnniversaryInRange(dtStart As Date, dtEnd As Date,
varAnniversary As Variant) As Boolean
boolAnniversaryInRange = IIF(IsNull(varAnniversary), False,
(Int(Format(DateAdd("d", -1, dtStart), "yyyy.mmdd") -
Format(varAnniversary, "yyyy.mmdd")) < Int(Format(dtEnd, "yyyy.mmdd") -
Format(dtAnniversary, "yyyy.mmdd"))))
End Function
The start and end dates can be on a form and the anniversary date can
come from your AnnivDate field in the table. Note: I didn't test the
function but briefly tested something like it in SQL form.
James A. Fortune
CDMAP...@FortuneJames.com