Match Date range with two digit Number (year)

[Barry A&P]

the contract we are working has a "Fiscal" type year that Typically runs Jun
1 to May 31? but our contract lingo is "option year" all of our charge
codes correspond to a certain "option Year" is it possible to use a table
that may contain fields that are something like PeriodStart PeriodEnd
OptionYear where my query would reference the Date and basically return the
option year to a table,,

Ie:
T_Time entries
EntryDate
TotalHours
OptionYear

I Want my update Query to update the Option year so
If Entry Date Between 6-1-07 and 5-30-08 OptionYear = 1

I proposed the conversion table so a new IF statement would not have to be
added every Year.

Sorry if i have been staring at this too long to ask a sensible question

Thanks in advance
Barry

 

[KARL DEWEY]

Try this --
OptionYear: Year(DateAdd("m", -6,[EntryDate]))-2006

This subtracts 6 months from EntryDate resulting in FY that is extracted
from the date and then 2006 is subtracted giving you the OptionYear.

 

[John Spencer MVP]

Your use of a table is a good idea. You could store the option year or you
could calculate it in your queries. I would say that the latter is generally
the better way to do this as it will handle automatically changes to the date
in "sometable". If you choose to store the option year in "sometable" you
will always have to make sure you are calculating the value when the data is
added or changed.

I think that a query that looks like the following will probably work for you
if you decide you need to update the OptionYear field in your table.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE SomeTable INNER JOIN OptionYearTable
ON SomeTable.SomeDate >= OptionYearTable.StartDate
AND SomeTable.SomeDate <= OptionYearTable.EndDate
SET SomeTable.OptionYear = [OptionYearTable].[OptionYear]

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

 

[Barry A&P]

Worked as Advertised
Everyday learning something new. since design view doesnt support the <=
join i didnt know it could be done

Thanks John

Your use of a table is a good idea. You could store the option year or you
could calculate it in your queries. I would say that the latter is generally
the better way to do this as it will handle automatically changes to the date
in "sometable". If you choose to store the option year in "sometable" you
will always have to make sure you are calculating the value when the data is
added or changed.

I think that a query that looks like the following will probably work for you
if you decide you need to update the OptionYear field in your table.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE SomeTable INNER JOIN OptionYearTable
ON SomeTable.SomeDate >= OptionYearTable.StartDate
AND SomeTable.SomeDate <= OptionYearTable.EndDate
SET SomeTable.OptionYear = [OptionYearTable].[OptionYear]

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

the contract we are working has a "Fiscal" type year that Typically runs Jun
1 to May 31? but our contract lingo is "option year" all of our charge
codes correspond to a certain "option Year" is it possible to use a table
that may contain fields that are something like PeriodStart PeriodEnd
OptionYear where my query would reference the Date and basically return the
option year to a table,,

Ie:
T_Time entries
EntryDate
TotalHours
OptionYear

I Want my update Query to update the Option year so
If Entry Date Between 6-1-07 and 5-30-08 OptionYear = 1

I proposed the conversion table so a new IF statement would not have to be
added every Year.

Sorry if i have been staring at this too long to ask a sensible question

Thanks in advance
Barry

 

[Barry A&P]

Thank you Karl for your input..
The suggestion you made still returns a "Date" Value and i am trying to Join
to a Number Value.. and i get a data mismatch error. John spencers query is
getting me where i wanted to go "so far"

Try this --
OptionYear: Year(DateAdd("m", -6,[EntryDate]))-2006

This subtracts 6 months from EntryDate resulting in FY that is extracted
from the date and then 2006 is subtracted giving you the OptionYear.

the contract we are working has a "Fiscal" type year that Typically runs Jun
1 to May 31? but our contract lingo is "option year" all of our charge
codes correspond to a certain "option Year" is it possible to use a table
that may contain fields that are something like PeriodStart PeriodEnd
OptionYear where my query would reference the Date and basically return the
option year to a table,,

Ie:
T_Time entries
EntryDate
TotalHours
OptionYear

I Want my update Query to update the Option year so
If Entry Date Between 6-1-07 and 5-30-08 OptionYear = 1

I proposed the conversion table so a new IF statement would not have to be
added every Year.

Sorry if i have been staring at this too long to ask a sensible question

Thanks in advance
Barry