Re: Calculating a field for a date range

Pete--

My bad on the reply. Your code snippet just required (per your instruction) a few minor mods. The final result was (for MSAccess):

AY: CStr(Year(DateAdd("m",-6,[RECEIVED_DATE]))) & CStr(Year(DateAdd ("m",-6,[RECEIVED_DATE]))+1)

Tada..works like a champ.

As for the following question I had:

>Should there be
> something in there that I tell it all days between July 1 and June 30
> are the Academic Year of that year?

Yes of course, that is the +1 (doh!)

Thanks again.

Cheers,

Sean

In article <8or92r$p0p$1_at_nnrp1.deja.com>,   sean_semone_at_my-deja.com wrote:
> Hey Pete--
>
> Thanks for the quick reply. I tried these out, but didn't have any
> luck. In the Access code I can't figure out how the select statement
> determines when the Academic Year begins and ends. >
> Thanks again!
>
> Cheers,
>
> Sean
>
> In article <SNVr5.7647$f65.366909_at_news-west.usenetserver.com>,
> "Pete" <pachavez_at_excite.com> wrote:
> > I answered this from work but it didn't get through. Here is the
 solution in
> > Access and Oracle syntax.
> >
> > Access:
> > select CStr(Year(DateAdd("m",-6,[RECEIVED_DATE]))) &
> > CStr(Year(DateAdd("m",-6,[RECEIVED_DATE]))+1) AS AY from
 [yourTable];
> >
> > Oracle
> > select to_char(round(RECEIVED_DATE,'YYYY'),'YYYY') ||
> > to_char(round(add_months(RECEIVED_DATE,12),'YYYY'),'YYYY') from
 yourTable;
> >
> > I haven't tested either but it should work with little or no
 alterations.
> >
> > Pete
> > <sean_semone_at_my-deja.com> wrote in message
> > news:8oorlk$4sj$1_at_nnrp1.deja.com...
> > > I need to do the following:
> > >
> > > I have a date field and need to group on the date field based on a
> > > range of dates. For instance all dates between 7/1/1998 and
 6/30/1999
> > > need to be calculated as the string 19981999.
> > >
> > > Right now I am doing this in Access looking at data on an Oracle 8
> > > Database with the following calculated field:
> > >
> > > AY: IIf([RECEIVED_DATE] Between #7/1/1989# And
 #6/30/1990#,19891990,
> > > ([RECEIVED_DATE] Between #7/1/1990# And #6/30/1991#,19801981, ....
> > >
> > > okay so you get the idea. This works great for 5 or ten years of
> > > ranges, but it would be nice if I could do something like the
 following
> > > pseudo-code:
> > >
> > >
> > > IIf([RECEIVED_DATE] Between #7/1/YYYY# And #6/30/YYYY#,
 (CONCATENATE
> > > ([RECEIVED_DATE #YYYY#]),[RECEIVED_DATE #YYYY#+1])),"n/a")
> > >
> > > Of course that doesn't work but it is 'lgically' what I would like
 to
> > > do.
> > >
> > > There is also a "Round" (see table 3-11 in the oracle 8 SQL
 command
> > > reference) function that can be used with dates and times in
 Oracle
 but
> > > I couldn't get that to work, although it looks like it might do
 the
> > > trick if I could round the year from the date in the file up (on
> > > 7/1/YYYY and then concatenate that result with the previous year.
> > >
> > > Anyway...that is where I am..I'm a moving from SQL Server to
 Oracle
 so
> > > any other links/texts you can point me to would be great.
> > >
> > > Thanks,
> > >
> > > Sean
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 02 2000 - 13:43:21 CDT