Having trouble with Date calculation Query logic

[Dragon]

Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display the
fir three records from this data. Essentially calculate the date/month to
see if the date/month are within the (# of Days) period from today. You can
say it is similar to figuring our if your birthday is coming up with x
number of days.

If the date is in the past, it will act as it telling you if your birthday
is coming up within x number of days. If the date is in future, it will act
as if it is a reminder for that date if that date is within x number of
days.

thanks.

Thank you.

 

[strive4peace]

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Dragon]

Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to
explain

Having said that I do not believe your solution will work. It will pickup
all date prior to (Date() + NumDays) while I only want date where month/day
combination is within range. To explain this a bit more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because month/date
combination is not within the specified range (date() + NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display
the fir three records from this data. Essentially calculate the
date/month to see if the date/month are within the (# of Days) period
from today. You can say it is similar to figuring our if your birthday is
coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in future,
it will act as if it is a reminder for that date if that date is within x
number of days.

thanks.

Thank you.

 

[strive4peace]

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when
the NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26,
2005 (FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to
explain

Having said that I do not believe your solution will work. It will pickup
all date prior to (Date() + NumDays) while I only want date where month/day
combination is within range. To explain this a bit more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because month/date
combination is not within the specified range (date() + NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display
the fir three records from this data. Essentially calculate the
date/month to see if the date/month are within the (# of Days) period
from today. You can say it is similar to figuring our if your birthday is
coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in future,
it will act as if it is a reminder for that date if that date is within x
number of days.

thanks.

Thank you.

 

[Dragon]

I am sorry if I am not making much sense here. I will try again.

If the date field has a date in the past, we only want to compare the month
and date, not the year. If month and date combination is with this range
(date() + NoD) then it should be picked up regardless of the year.

In my example December 26, 2005 will be picked up because date is in the
past (like a birthdate) and day/month combination (December 26) is within
the range (Today: December 24 + NoD=16)

If the date field has a date in the future, then you also consider the year
to see if that date falls within range. In this case something like this
would work: If date <= (Date() + NoD) and date >= Date() Then True

I hope it makes sense now.

Thanks.

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when the
NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005
(FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to
explain

Having said that I do not believe your solution will work. It will pickup
all date prior to (Date() + NumDays) while I only want date where
month/day combination is within range. To explain this a bit more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because
month/date combination is not within the specified range (date() + NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved
word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display
the fir three records from this data. Essentially calculate the
date/month to see if the date/month are within the (# of Days) period
from today. You can say it is similar to figuring our if your birthday
is coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in
future, it will act as if it is a reminder for that date if that date
is within x number of days.

thanks.

Thank you.

 

[John Spencer (MVP)]

Let me rephrase the problem to confirm my understanding.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

Then Return record for
Task 1 If today is between Jan 1 and Jan 17
Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8
Task 3 If today is between Jan 5 and Jan 25
Task 4 if today is between Jan 25 and Jan 30
Task 5 if today is between Apr 9 and Apr 24

I think the following may work for you.
SELECT *
FROM Table
WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateField))
and DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)
OR
Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(DateField)) and
DateSerial(Year(Date())+1,Month(DateField),Day(DateField)+ NumDays)

The second criteria is to handle dates at the end of the year (such as Dec 25)
and could also be written as

DateAdd("yyyy",1,Date()) Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry if I am not making much sense here. I will try again.

If the date field has a date in the past, we only want to compare the month
and date, not the year. If month and date combination is with this range
(date() + NoD) then it should be picked up regardless of the year.

In my example December 26, 2005 will be picked up because date is in the
past (like a birthdate) and day/month combination (December 26) is within
the range (Today: December 24 + NoD=16)

If the date field has a date in the future, then you also consider the year
to see if that date falls within range. In this case something like this
would work: If date <= (Date() + NoD) and date >= Date() Then True

I hope it makes sense now.

Thanks.

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when the
NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005
(FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to
explain

Having said that I do not believe your solution will work. It will pickup
all date prior to (Date() + NumDays) while I only want date where
month/day combination is within range. To explain this a bit more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because
month/date combination is not within the specified range (date() + NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved
word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display
the fir three records from this data. Essentially calculate the
date/month to see if the date/month are within the (# of Days) period
from today. You can say it is similar to figuring our if your birthday
is coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in
future, it will act as if it is a reminder for that date if that date
is within x number of days.

thanks.

Thank you.

 

[Dragon]

John,

We could be talking about the same thing but from different angle but I am a
bit confused so I will use your approach to clarify things.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

And Today being December 24, 2008

Then Return record for
Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from
Today (Jan 09, 2009) = True in this case
Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from
Today (Jan 7, 2009) = True in this case
Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days
from Today (Jan 13, 2009) = True in this case
Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days
from Today (Dec 29, 2008) = False in this case
Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from
Today (Jan 08, 2009) = False in this case

The Login I can come up with is:

If MyDate < Date () Then
MyMonth = Month (MyDate)
MyDay = Day(MyDate)
If (MyMonthMyDay) >= MonthDay(Date()) and (MyMonthMyDay) <=
MonthDay(Date()) + NoD Then
Select Record = True
Else If MyDate < Date () + NoD then
Select Record = True
Else
Select record = False

If it still doesn't make sense and I am confusing everyone, then I think I
am simply not capable of explaining this right One way to think if this
problem is that it has two parts.
- Find out if a birthday is coming up within a given period. Typically you
will assume the given period to be fixed, say 14 days, but in this case each
birthday has a variable period.And since birthday dates are in the past, you
have to ignore the year when calculating.
- Find out if there is an upcoming event coming up within a given period for
each event. Since events are in the future, you have to make sure look at
the year as well and not display event for someting that isn't due for a few
years.

Let me rephrase the problem to confirm my understanding.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

Then Return record for
Task 1 If today is between Jan 1 and Jan 17
Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8
Task 3 If today is between Jan 5 and Jan 25
Task 4 if today is between Jan 25 and Jan 30
Task 5 if today is between Apr 9 and Apr 24

I think the following may work for you.
SELECT *
FROM Table
WHERE Date() Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)
OR
Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(DateField))
and DateSerial(Year(Date())+1,Month(DateField),Day(DateField)+ NumDays)

The second criteria is to handle dates at the end of the year (such as Dec
25)
and could also be written as

DateAdd("yyyy",1,Date()) Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry if I am not making much sense here. I will try again.

If the date field has a date in the past, we only want to compare the
month and date, not the year. If month and date combination is with this
range (date() + NoD) then it should be picked up regardless of the year.

In my example December 26, 2005 will be picked up because date is in the
past (like a birthdate) and day/month combination (December 26) is within
the range (Today: December 24 + NoD=16)

If the date field has a date in the future, then you also consider the
year to see if that date falls within range. In this case something like
this would work: If date <= (Date() + NoD) and date >= Date() Then True

I hope it makes sense now.

Thanks.

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when
the NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26,
2005 (FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Thank you Crystal for your reply. My sample didn't have the actual
field names. I used these words (Date, # of Days etc) just to make it
easier to explain

Having said that I do not believe your solution will work. It will
pickup all date prior to (Date() + NumDays) while I only want date
where month/day combination is within range. To explain this a bit
more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because
month/date combination is not within the specified range (date() +
NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to
use spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved
word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Hi,

I am trying to create a query which performs some date calculations
and pulls records based on the calculation. I think it will be
simpler to explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will
display the fir three records from this data. Essentially calculate
the date/month to see if the date/month are within the (# of Days)
period from today. You can say it is similar to figuring our if your
birthday is coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in
future, it will act as if it is a reminder for that date if that date
is within x number of days.

thanks.

Thank you.

 

[Ken Sheridan]

John:

Shouldn't it be:

SELECT *
FROM Table
WHERE DATE() BETWEEN DATESERIAL(YEAR(DATE()),MONTH(DateField),DAY(DateField))
AND DateSerial(YEAR(DATE()),MONTH(DateField),DAY(DateField)+ NumDays)
OR
DATE() BETWEEN
DATESERIAL(YEAR(DATE())-1,MONTH(DateField),DAY(DateField))
AND DATESERIAL(YEAR(DATE())-1,MONTH(DateField),DAY(DateField)+ NumDays)

For end-of year DateField values, if the current date falls within the range
but in the new year, then I think you'd have to start the range from last
year, not next year.

Ken Sheridan
Stafford, England

Let me rephrase the problem to confirm my understanding.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

Then Return record for
Task 1 If today is between Jan 1 and Jan 17
Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8
Task 3 If today is between Jan 5 and Jan 25
Task 4 if today is between Jan 25 and Jan 30
Task 5 if today is between Apr 9 and Apr 24

I think the following may work for you.
SELECT *
FROM Table
WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateField))
and DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)
OR
Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(DateField)) and
DateSerial(Year(Date())+1,Month(DateField),Day(DateField)+ NumDays)

The second criteria is to handle dates at the end of the year (such as Dec 25)
and could also be written as

DateAdd("yyyy",1,Date()) Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry if I am not making much sense here. I will try again.

If the date field has a date in the past, we only want to compare the month
and date, not the year. If month and date combination is with this range
(date() + NoD) then it should be picked up regardless of the year.

In my example December 26, 2005 will be picked up because date is in the
past (like a birthdate) and day/month combination (December 26) is within
the range (Today: December 24 + NoD=16)

If the date field has a date in the future, then you also consider the year
to see if that date falls within range. In this case something like this
would work: If date <= (Date() + NoD) and date >= Date() Then True

I hope it makes sense now.

Thanks.

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when the
NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005
(FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to
explain

Having said that I do not believe your solution will work. It will pickup
all date prior to (Date() + NumDays) while I only want date where
month/day combination is within range. To explain this a bit more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because
month/date combination is not within the specified range (date() + NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to use
spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved
word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Hi,

I am trying to create a query which performs some date calculations and
pulls records based on the calculation. I think it will be simpler to
explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will display
the fir three records from this data. Essentially calculate the
date/month to see if the date/month are within the (# of Days) period
from today. You can say it is similar to figuring our if your birthday
is coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in
future, it will act as if it is a reminder for that date if that date
is within x number of days.

thanks.

Thank you.

 

[John Spencer (MVP)]

OK. *IF* I understand correctly the where clause would look like the following.

WHERE DateSerial(Year(Date()),Month(MyDate),Day(MyDate)) Between Date() and
DateAdd("d",NumDays,Date())
OR
DateSerial(Year(Date())+1,Month(MyDate),Day(MyDate)) Between Date() and
DateAdd("d",NumDays,Date())

For Task1 that equates to
1/1/2008 between 12/24/08 and 1/9/2009
or 1/1/2009 between 12/24/08 and 1/9/2009

For Task5 that equates to
4/9/2008 between 12/24/2008 and 1/8/2009
or 4/9/2009 between 12/24/2008 and 1/8/2009

Bench testing indicated you should get the desired results.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

We could be talking about the same thing but from different angle but I am a
bit confused so I will use your approach to clarify things.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

And Today being December 24, 2008

Then Return record for
Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from
Today (Jan 09, 2009) = True in this case
Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from
Today (Jan 7, 2009) = True in this case
Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days
from Today (Jan 13, 2009) = True in this case
Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days
from Today (Dec 29, 2008) = False in this case
Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from
Today (Jan 08, 2009) = False in this case

The Login I can come up with is:

If MyDate < Date () Then
MyMonth = Month (MyDate)
MyDay = Day(MyDate)
If (MyMonthMyDay) >= MonthDay(Date()) and (MyMonthMyDay) <=
MonthDay(Date()) + NoD Then
Select Record = True
Else If MyDate < Date () + NoD then
Select Record = True
Else
Select record = False

If it still doesn't make sense and I am confusing everyone, then I think I
am simply not capable of explaining this right One way to think if this
problem is that it has two parts.
- Find out if a birthday is coming up within a given period. Typically you
will assume the given period to be fixed, say 14 days, but in this case each
birthday has a variable period.And since birthday dates are in the past, you
have to ignore the year when calculating.
- Find out if there is an upcoming event coming up within a given period for
each event. Since events are in the future, you have to make sure look at
the year as well and not display event for someting that isn't due for a few
years.

Let me rephrase the problem to confirm my understanding.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

Then Return record for
Task 1 If today is between Jan 1 and Jan 17
Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8
Task 3 If today is between Jan 5 and Jan 25
Task 4 if today is between Jan 25 and Jan 30
Task 5 if today is between Apr 9 and Apr 24

I think the following may work for you.
SELECT *
FROM Table
WHERE Date() Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)
OR
Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(DateField))
and DateSerial(Year(Date())+1,Month(DateField),Day(DateField)+ NumDays)

The second criteria is to handle dates at the end of the year (such as Dec
25)
and could also be written as

DateAdd("yyyy",1,Date()) Between
DateSerial(Year(Date()),Month(DateField),Day(DateField)) and
DateSerial(Year(Date()),Month(DateField),Day(DateField)+ NumDays)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry if I am not making much sense here. I will try again.

If the date field has a date in the past, we only want to compare the
month and date, not the year. If month and date combination is with this
range (date() + NoD) then it should be picked up regardless of the year.

In my example December 26, 2005 will be picked up because date is in the
past (like a birthdate) and day/month combination (December 26) is within
the range (Today: December 24 + NoD=16)

If the date field has a date in the future, then you also consider the
year to see if that date falls within range. In this case something like
this would work: If date <= (Date() + NoD) and date >= Date() Then True

I hope it makes sense now.

Thanks.

Hi Dragon,

I did not really follow what you wanted -- just looked at the first 3
records, which you said you wanted and ignored what you wrote because it
seemed to contradict that

so you want everything between Today and whatever day is will be when
the NumDays comes around? like this:

WHERE DateField BETWEEN Date() and (Date() + NumDays)
??

Your test date is Dec 24, 2008 -- why would that pick up December 26,
2005 (FIVE)? -- or is that a typo? that is what was confusing me...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Thank you Crystal for your reply. My sample didn't have the actual
field names. I used these words (Date, # of Days etc) just to make it
easier to explain

Having said that I do not believe your solution will work. It will
pickup all date prior to (Date() + NumDays) while I only want date
where month/day combination is within range. To explain this a bit
more:

Today's Date: Dec 24, 2008
# of Days (NoD): 16
Date1 : December 26, 2005
Date 2: April 05, 2008
Date 3: Jan 03, 2009.

Query should pickup only Date 1 and Date 3 and not Date 2 because
month/date combination is not within the specified range (date() +
NoD).

Thanks.

Hi Dragon,

SELECT *
FROM Tablename
WHERE DateField <= (Date() + NumDays)


I used NumDays instead of [# of Days] since it is not a good idea to
use spaces or special characters (except underscore) in fieldnames

btw, do not use DATE as a fieldname or controlname, it is a reserved
word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dragon wrote:
Hi,

I am trying to create a query which performs some date calculations
and pulls records based on the calculation. I think it will be
simpler to explain with an example;

Data:
Task Date # of Days
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

If today is December 24, 2008, Query I am trying to write will
display the fir three records from this data. Essentially calculate
the date/month to see if the date/month are within the (# of Days)
period from today. You can say it is similar to figuring our if your
birthday is coming up with x number of days.

If the date is in the past, it will act as it telling you if your
birthday is coming up within x number of days. If the date is in
future, it will act as if it is a reminder for that date if that date
is within x number of days.

thanks.

Thank you.

 

[James A. Fortune]

John,

We could be talking about the same thing but from different angle but I am a
bit confused so I will use your approach to clarify things.

Given
Task1 Jan 01, 1980 16
Task 2 Dec 25, 2007 14
Task 3 Jan 05, 2009 20
Task 4 Jan 25, 2010 5
Task 5 Apr 09, 1995 15

And Today being December 24, 2008

Then Return record for
Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from
Today (Jan 09, 2009) = True in this case
Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from
Today (Jan 7, 2009) = True in this case
Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days
from Today (Jan 13, 2009) = True in this case
Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days
from Today (Dec 29, 2008) = False in this case
Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from
Today (Jan 08, 2009) = False in this case

Here is an alternate WHERE condition:

Based on:

http://groups.google.com/group/comp.databases.ms-access/msg/793f5063bdd83451

Try:

SELECT * FROM Table WHERE Int(Format(DateAdd("d", -1, Date()),
"yyyy.mmdd") - Format(DateField, "yyyy.mmdd")) < Int(Format(DateAdd("d",
NumDays, Date()), "yyyy.mmdd") - Format(DateField, "yyyy.mmdd"));

That is, if the age in years from the anniversary/birthday date is the
same for the start (minus 1) and end dates, then there's no change
within the period in question.

James A. Fortune
(e-mail address removed)