I want a date (range).

[Guest]

In a query, I have a field (combineddate) based on a union query which
combines 2 dates - dob (date of birth) and doa (date of anniversary). In some
instances, the year is not known and I've used the year "104" for those. With
help from the board, I now have the query returning mm/dd ONLY. I use this
info for a report which lists upcoming dates (sorted by month, then day) in
which I want to remember folks' birthdays and/or anniversaries.

The SQL statement for the union query is:
SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

The SQL statement for my "events" query is:
SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE (((qryUnionForEvents.CombinedDate) Is Not Null));

I now want to develop a mechanism for teasing out only those events which
will take place within the next 2 weeks. I've tried a variety of different
solutions - for example Between Date() And Date()+14 - but the queries come
up void when I run them. I suspect this is because the years range anywhere
from 104 to 2006.... all of which are dates that occured in the past.

How do I word the expression so that the YEAR will be eliminated from
consideration and only the month and day will be factored in?

Appreciate the help!

 

[John Spencer]

Sue,

Try the following

SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE Format(qryUnionForEvents.CombinedDate,"MM/DD")
Between Format(Date(),"MM/DD") AND Format(Date()+13,"MM/DD")

That should work EXCEPT for dates in the last days of December. The last two
weeks of December leads to problems in that the generated date range ends up
with a value like 01/11 for the end of the range. And Access will return
almost every record in the database. So a better where clause would be the
following

WHERE
DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between Date() and Date() + 13
OR
DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 13

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Sue:

First, I would put the test for null dates in the union query, for each of
the sub queries.

Second

WHERE DateSerial(Year(Date) + IIF(Month(CombinedDate) >= Month(Date()), 0, 1),
Month(CombinedDate), _
Day(CombinedDate)) BETWEEN Date() AND Date() + 14

 

[Guest]

Hi John -
Thanks for your kind assistance.

I tried the following:

SELECT qryUnionForEvents.LastName,
qryUnionForEvents.FirstName,qryUnionForEvents.CombinedDate,
qryUnionForEvents.CardType,Format(qryUnionForEvents.CombinedDate,"mm/dd")
AS EventDate,Format(qryUnionForEvents.CombinedDate,"mmmm") AS
EventMonth,Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between Date() and Date() + 13
OR DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 13

and got a data mismatch in expression error.
Did I misunderstand?
Thanks.

--
Thanks for your time!

Sue,

Try the following

SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE Format(qryUnionForEvents.CombinedDate,"MM/DD")
Between Format(Date(),"MM/DD") AND Format(Date()+13,"MM/DD")

That should work EXCEPT for dates in the last days of December. The last two
weeks of December leads to problems in that the generated date range ends up
with a value like 01/11 for the end of the range. And Access will return
almost every record in the database. So a better where clause would be the
following

WHERE
DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between Date() and Date() + 13
OR
DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 13

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

In a query, I have a field (combineddate) based on a union query which
combines 2 dates - dob (date of birth) and doa (date of anniversary). In
some
instances, the year is not known and I've used the year "104" for those.
With
help from the board, I now have the query returning mm/dd ONLY. I use this
info for a report which lists upcoming dates (sorted by month, then day)
in
which I want to remember folks' birthdays and/or anniversaries.

The SQL statement for the union query is:
SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

The SQL statement for my "events" query is:
SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE (((qryUnionForEvents.CombinedDate) Is Not Null));

I now want to develop a mechanism for teasing out only those events which
will take place within the next 2 weeks. I've tried a variety of different
solutions - for example Between Date() And Date()+14 - but the queries
come
up void when I run them. I suspect this is because the years range
anywhere
from 104 to 2006.... all of which are dates that occured in the past.

How do I word the expression so that the YEAR will be eliminated from
consideration and only the month and day will be factored in?

Appreciate the help!

 

[John Spencer]

If you are getting a data mismatch error then I would look for problems
in CombinedDate returning something other than a date. For instance, if
the dates you are "combining" are text fields you could have something
in the source field that could cause this to happen. For instance the
month of a date entered as Oh One instead of Zero One would generate a
Type Mismatch error.


SELECT LastName
, FirstName
, CombinedDate
, CardType
, Format(CombinedDate,"mm/dd") AS EventDate
, Format(CombinedDate,"mmmm") AS EventMonth
, Format(combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents

WHERE DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between Date() and Date() + 13

OR DateSerial(Year(Date()),Month([CombinedDate]),Day ([CombinedDate]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 13



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================