I want a date (range).

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
..
 
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
 
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!


John Spencer said:
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
..

Sue said:
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!
Click to expand...
Click to expand...
 
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
'====================================================
 
Back
Top