473,287 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Need help on MM/DD query; nothing I've tried gives me an end to end solution --Help please!

I have a 200 record database that includes a date/time field,
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

Jan 15 '07 #1
2 2048

Bill wrote:
I have a 200 record database that includes a date/time field,
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
In:

http://groups.google.com/group/comp....1f69113a7d219e

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
CD********@FortuneJames.com

Jan 15 '07 #2
CD********@FortuneJames.com wrote:
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
CD********@FortuneJames.com

Jan 16 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: John | last post by:
This message is popping up at startup and its saying "fatal execution engine error (0x7927e03e)" Ive tried deleting, reinstalling frameworks, restoring computer, but I can't use the os cd because i...
9
by: Daz | last post by:
Hello hello! I'm trying to finish off putting my design into HTML and I've come across a problem that I can't get my head around. I've got divs floating in two columns, but I'm having problems...
6
by: John Baker | last post by:
Hi: As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily...
23
by: Jason | last post by:
Hi, I was wondering if any could point me to an example or give me ideas on how to dynamically create a form based on a database table? So, I would have a table designed to tell my application...
1
by: teddymeu | last post by:
Hi Guys i write a thread before asking for help with reading an uploaded csv file, i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to...
6
by: leeg | last post by:
Help please. Apologies but I am poor in access and programming :o) and am having trouble getting my head around this one!!...again!!! I need to have a query or report to flag up someone who has...
2
by: andy2k8 | last post by:
Im interested in building a fantasy football site. I want to have the same features as the following site (www.mystify.co.uk) I have downloaded Microsoft Virtual Web deleloper, Sql server and...
6
by: spider661 | last post by:
im trying to make a perl file that will take all the info from a spell_us.txt file and place it into an sql file i can import into my database but its not working can anyone help please? ...
0
by: uno7031 | last post by:
Help Please!!! Adding 5 Days to another Date in an access query Good Morning, Help please…. I am new to access and trying to write a query that will add 5 days between a RecDate and a...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.