Lookup in a table? Or an Update Qry?

[Heather]

In our data we have a Contract End Dt from this we create two other fields
Renewal Year & Renewal Qtr manually

I'm wondering if there is a way to someone do an update qry that looks at
the Contract End Dt

for example:
Contract End Dt Ren FY YR Ren FY QTR
2/28/2011 2012 2012-1

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-1
2/2011
3/2011
4/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-2
5/2011
6/2011
7/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-3
8/2011
9/2011
10/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-4
11/2011
12/2011
1/2012

 

[John Spencer]

You can use a little date math to force the date into the future 11 months and
then get the year and quarter from that.

Format(DateAdd("m",11,[Contract End Dt]),"yyyy-q")

Or you can use the table solution. The table solution is more flexible,
especially if your periods are not standard. For example, you have 13
accounting periods in a year, or your months run from the first monday of the
month to the day before the first monday of the next month.

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

 

[Heather]

thank you = our periods aren't standard, but is there a way to trim the
Contract End Dt so that I can see what month/year it's showing rather than
how it's coming thru now at a full month/day/year format

You can use a little date math to force the date into the future 11 months and
then get the year and quarter from that.

Format(DateAdd("m",11,[Contract End Dt]),"yyyy-q")

Or you can use the table solution. The table solution is more flexible,
especially if your periods are not standard. For example, you have 13
accounting periods in a year, or your months run from the first monday of the
month to the day before the first monday of the next month.

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

In our data we have a Contract End Dt from this we create two other fields
Renewal Year & Renewal Qtr manually

I'm wondering if there is a way to someone do an update qry that looks at
the Contract End Dt

for example:
Contract End Dt Ren FY YR Ren FY QTR
2/28/2011 2012 2012-1

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-1
2/2011
3/2011
4/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-2
5/2011
6/2011
7/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-3
8/2011
9/2011
10/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-4
11/2011
12/2011
1/2012

 

[John Spencer]

Use the FORMAT function.

Format(SomeDate,"MM")
Format(SomeDate,"yyyy")
Format(SomeDate,"yyyy-mm")

Look up the format function in the help for more ideas. Also check out
DatePart function which will return numbers instead of a string of
number characters.

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

thank you = our periods aren't standard, but is there a way to trim the
Contract End Dt so that I can see what month/year it's showing rather than
how it's coming thru now at a full month/day/year format

You can use a little date math to force the date into the future 11 months and
then get the year and quarter from that.

Format(DateAdd("m",11,[Contract End Dt]),"yyyy-q")

Or you can use the table solution. The table solution is more flexible,
especially if your periods are not standard. For example, you have 13
accounting periods in a year, or your months run from the first monday of the
month to the day before the first monday of the next month.

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

In our data we have a Contract End Dt from this we create two other fields
Renewal Year & Renewal Qtr manually

I'm wondering if there is a way to someone do an update qry that looks at
the Contract End Dt

for example:
Contract End Dt Ren FY YR Ren FY QTR
2/28/2011 2012 2012-1

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-1
2/2011
3/2011
4/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-2
5/2011
6/2011
7/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-3
8/2011
9/2011
10/2011

IF Contract End Dt is in these 3 months it's YR 2012 and Qtr 2012-4
11/2011
12/2011
1/2012