Comparing Dates

[jlieffort via AccessMonster.com]

I have a table with InventoryID's each one has a season start date and end
date, I want to be able to determine if "today" is in Season or not. The
start and end dates are different depending on the InventoryID.

The season start and end dates are in typical date form in the table, so in
my query, I could compare "today" Now() and see if it's greater than begin
season date and less than end season date if yes then "InSeason".

Which will work this year, but in subsequent years I will have to change all
the begin and end season dates, which I don't want to do.

Any ideas will be greatly appreciated

Thanks

 

[RonaldoOneNil]

What is the problem with updating the season begin and end dates ?
You can do it with a query or code using the DateAdd function.
DateAdd("yyyy",1,[DateFieldName])

 

[jlieffort via AccessMonster.com]

Thanks for your response,

I can do that but I would prefer not to.

IS there a way to set a season start and end date (perhaps in the table)
which could overlap a year in some instances and then compare a date to see
if it falls within the period, year after year.

This is a scheduling application where the season begin and end dates will
rarely change.

Thanks

What is the problem with updating the season begin and end dates ?
You can do it with a query or code using the DateAdd function.
DateAdd("yyyy",1,[DateFieldName])

I have a table with InventoryID's each one has a season start date and end
date, I want to be able to determine if "today" is in Season or not. The

[quoted text clipped - 10 lines]

Thanks

 

[John Spencer]

You can also just set your compare action to use something like the following.
This assumes that the in season dates are all set for 2008.

DateSerial(2008,Month(YourField),Day(YourField)) BETWEEN InSeasonStart and
InSeasonEnd

SO next year you will be still be able to use inseason dates that have 2008 as
the year.

You could also store the inseason dates as a mmdd string and compare that

Format(YourDate,"mmdd") >=InSeasonStart and Format(YourDate,"mmdd")<= InSeasonEnd

If your season overlaps years Dec 1, 2008 to Jan 31, 2009 you would need to
set up two records in order to handle the comparison
1201 1231
0101 0131

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

What is the problem with updating the season begin and end dates ?
You can do it with a query or code using the DateAdd function.
DateAdd("yyyy",1,[DateFieldName])

I have a table with InventoryID's each one has a season start date and end
date, I want to be able to determine if "today" is in Season or not. The
start and end dates are different depending on the InventoryID.

The season start and end dates are in typical date form in the table, so in
my query, I could compare "today" Now() and see if it's greater than begin
season date and less than end season date if yes then "InSeason".

Which will work this year, but in subsequent years I will have to change all
the begin and end season dates, which I don't want to do.

Any ideas will be greatly appreciated

Thanks

 

[jlieffort via AccessMonster.com]

Thanks, I may not be quite following you, perhaps if I explain what I'm
trying to do, there may be a better tact.

I work for a large tree farm, we are building a system to prompt us when to
do certain tasks to our trees, like pruning. Our Tree experts say Tree "A"
grows from Nov1 to Mar30 and we want to prune it every 45 days during that
period. Tree B grows From Jun1 to Nov.15 and we want to prune that tree every
30 days during that period.

So I have over 1,000 types of trees that have different start and end season
dates.

My query needs to look at the inventory decide whether each inventory item is
in season or not based on today's date, then project the next pruning date.

Because all the start and end dates can overlap between items it might
require constantly looking at the dates and changing them to compensate for
the current time. It seems other businesses must have a similar issue.

My tact so far has been to convert the begin and end dates using the DatePart
function to convert them to the day of the year, which works fine if the
times were always start date after 1/1 and end before 12/31, or vice versa,
but this is not always the case.

Thanks,

You can also just set your compare action to use something like the following.
This assumes that the in season dates are all set for 2008.

DateSerial(2008,Month(YourField),Day(YourField)) BETWEEN InSeasonStart and
InSeasonEnd

SO next year you will be still be able to use inseason dates that have 2008 as
the year.

You could also store the inseason dates as a mmdd string and compare that

Format(YourDate,"mmdd") >=InSeasonStart and Format(YourDate,"mmdd")<= InSeasonEnd

If your season overlaps years Dec 1, 2008 to Jan 31, 2009 you would need to
set up two records in order to handle the comparison
1201 1231
0101 0131

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

What is the problem with updating the season begin and end dates ?
You can do it with a query or code using the DateAdd function.

[quoted text clipped - 14 lines]

 

[Douglas J. Steele]

What do you actually have stored in your Start and End date fields? Are they
dates, or just text?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, I may not be quite following you, perhaps if I explain what I'm
trying to do, there may be a better tact.

I work for a large tree farm, we are building a system to prompt us when
to
do certain tasks to our trees, like pruning. Our Tree experts say Tree "A"
grows from Nov1 to Mar30 and we want to prune it every 45 days during that
period. Tree B grows From Jun1 to Nov.15 and we want to prune that tree
every
30 days during that period.

So I have over 1,000 types of trees that have different start and end
season
dates.

My query needs to look at the inventory decide whether each inventory item
is
in season or not based on today's date, then project the next pruning
date.

Because all the start and end dates can overlap between items it might
require constantly looking at the dates and changing them to compensate
for
the current time. It seems other businesses must have a similar issue.

My tact so far has been to convert the begin and end dates using the
DatePart
function to convert them to the day of the year, which works fine if the
times were always start date after 1/1 and end before 12/31, or vice
versa,
but this is not always the case.

Thanks,

You can also just set your compare action to use something like the
following.
This assumes that the in season dates are all set for 2008.

DateSerial(2008,Month(YourField),Day(YourField)) BETWEEN InSeasonStart and
InSeasonEnd

SO next year you will be still be able to use inseason dates that have
2008 as
the year.

You could also store the inseason dates as a mmdd string and compare that

Format(YourDate,"mmdd") >=InSeasonStart and Format(YourDate,"mmdd")<=
InSeasonEnd

If your season overlaps years Dec 1, 2008 to Jan 31, 2009 you would need
to
set up two records in order to handle the comparison
1201 1231
0101 0131

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

What is the problem with updating the season begin and end dates ?
You can do it with a query or code using the DateAdd function.

[quoted text clipped - 14 lines]

Thanks

 

[jlieffort via AccessMonster.com]

dates

What do you actually have stored in your Start and End date fields? Are they
dates, or just text?

Thanks, I may not be quite following you, perhaps if I explain what I'm
trying to do, there may be a better tact.

[quoted text clipped - 62 lines]

 

[Douglas J. Steele]

Your example states "Our Tree experts say Tree "A" grows from Nov1 to Mar30
and we want to prune it every 45 days during that period. Tree B grows From
Jun1 to Nov.15 and we want to prune that tree every 30 days during that
period."

What dates are you storing to indicate Nov1 to Mar30 or Jun1 to Nov 15?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

dates

What do you actually have stored in your Start and End date fields? Are
they
dates, or just text?

Thanks, I may not be quite following you, perhaps if I explain what I'm
trying to do, there may be a better tact.

[quoted text clipped - 62 lines]

Thanks

 

[jlieffort via AccessMonster.com]

You're right I wasn't clear. I gave my tree experts a list of all their trees,
with open fields asking them for begin season end season they filled them in
with the dates including the years.

Tree A--11/1/2008 to 3/30/2009
Tree B-- 6/1/2009 to 11/15/2009

Thanks

Your example states "Our Tree experts say Tree "A" grows from Nov1 to Mar30
and we want to prune it every 45 days during that period. Tree B grows From
Jun1 to Nov.15 and we want to prune that tree every 30 days during that
period."

What dates are you storing to indicate Nov1 to Mar30 or Jun1 to Nov 15?

dates

[quoted text clipped - 7 lines]

 

[Douglas J. Steele]

Okay, if you compare Month([StartDate]) to Month([EndDate]), you'll know
whether it spans a year.

If it doesn't span a year, you should be able to simply use BETWEEN
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]) AND
DateSerial(Year(Date), Month([EndDate]), Day([EndDate]).

If it does span a year, you'll want either BETWEEN DateSerial(Year(Date),
Month([StartDate]), Day([StartDate])) AND DateSerial(Year(Date)+1,
Month([EndDate]), Day([EndDate])) or BETWEEN DateSerial(Year(Date)-1,
Month([StartDate]), Day([StartDate])) AND DateSerial(Year(Date),
Month([EndDate]), Day([EndDate])) depending on whether the month/date of the
current date is before or after the month/date of the Start Date. (In other
words, for tree A, you'd want the first formula if it's currently November
or December, but you'd want the second formula if it's currently January,
February or March.)

You can do this a single WHERE clause, but it's ugly <g>

WHERE Date BETWEEN IIf(Month[StartDate]) < Month([EndDate]),
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]),IIf(Month(Date)

=Month([StartDate]), DateSerial(Year(Date), Month([StartDate]),

Day([StartDate])), DateSerial(Year(Date) - 1, Month([StartDate]),
Day([StartDate]))))
AND IIf(Month[StartDate]) < Month([EndDate]), DateSerial(Year(Date),
Month([EndDate]), Day([EndDate]),IIf(Month(Date) >=Month([StartDate]),
DateSerial(Year(Date)+1, Month([StartDate]), Day([StartDate])),
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]))))

(hopefully I've got the parentheses correct in there...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You're right I wasn't clear. I gave my tree experts a list of all their
trees,
with open fields asking them for begin season end season they filled them
in
with the dates including the years.

Tree A--11/1/2008 to 3/30/2009
Tree B-- 6/1/2009 to 11/15/2009

Thanks

Your example states "Our Tree experts say Tree "A" grows from Nov1 to
Mar30
and we want to prune it every 45 days during that period. Tree B grows
From
Jun1 to Nov.15 and we want to prune that tree every 30 days during that
period."

What dates are you storing to indicate Nov1 to Mar30 or Jun1 to Nov 15?

dates

[quoted text clipped - 7 lines]

Thanks

 

[jlieffort via AccessMonster.com]

Thank you for your time and attention to my challenge.

I was hoping there might be an easier way, but I follow your methodolgy.
I will be working on it later this week and if have any issues I'll post.

Thanks again!

Jim

Okay, if you compare Month([StartDate]) to Month([EndDate]), you'll know
whether it spans a year.

If it doesn't span a year, you should be able to simply use BETWEEN
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]) AND
DateSerial(Year(Date), Month([EndDate]), Day([EndDate]).

If it does span a year, you'll want either BETWEEN DateSerial(Year(Date),
Month([StartDate]), Day([StartDate])) AND DateSerial(Year(Date)+1,
Month([EndDate]), Day([EndDate])) or BETWEEN DateSerial(Year(Date)-1,
Month([StartDate]), Day([StartDate])) AND DateSerial(Year(Date),
Month([EndDate]), Day([EndDate])) depending on whether the month/date of the
current date is before or after the month/date of the Start Date. (In other
words, for tree A, you'd want the first formula if it's currently November
or December, but you'd want the second formula if it's currently January,
February or March.)

You can do this a single WHERE clause, but it's ugly <g>

WHERE Date BETWEEN IIf(Month[StartDate]) < Month([EndDate]),
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]),IIf(Month(Date)

=Month([StartDate]), DateSerial(Year(Date), Month([StartDate]),

Day([StartDate])), DateSerial(Year(Date) - 1, Month([StartDate]),
Day([StartDate]))))
AND IIf(Month[StartDate]) < Month([EndDate]), DateSerial(Year(Date),
Month([EndDate]), Day([EndDate]),IIf(Month(Date) >=Month([StartDate]),
DateSerial(Year(Date)+1, Month([StartDate]), Day([StartDate])),
DateSerial(Year(Date), Month([StartDate]), Day([StartDate]))))

(hopefully I've got the parentheses correct in there...)

You're right I wasn't clear. I gave my tree experts a list of all their
trees,

[quoted text clipped - 21 lines]