Tagging
in [General]
|
Prev: A problem occurred while xfile was communicating with the OLE serv
Next: Can A Tickbox Backcolor Change & If So How Would It Be Done??
From: zyus on 9 Apr 2010 05:43 In my table i will have two months set of data. my text field [yr] and [mth] will determine the year & month. Sample of data is as follow [yr] [mth] [acno] [amt] 2010 01 123 100.00 2010 02 321 200.00 2010 02 123 150.00 What i want to achieve here is how to tag the latest month as "Latest Mth" and the month before latest month as "Previous Month". My new query/rpt will be [yr] [mth] [acno] [amt] [tag] 2010 01 123 100.00 "Previous Month" 2010 02 321 200.00 "Latest Mth" 2010 02 123 150.00 "Latest Mth" Please note that next month i will have different set of month data where 2010-02 & 2010-03. Thanks
From: Tom van Stiphout on 9 Apr 2010 09:49 On Fri, 9 Apr 2010 02:43:02 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: Yr and Mth should be numeric fields. Then later use the Format function if you want leading zeros etc. That said, to do the tagging the general concept would be that you would need to know what the latest month is: that's done with a Totals query with a Max function. Probably easiest way would be to convert your fields to a real date using the DateSerial function (using 1 as the Day number). Records with this date would get the "Latest Month" tag. Once you have that, you can go back one month with the DateAdd function. Records with this date would get the "Previous Month" tag. -Tom. Microsoft Access MVP >In my table i will have two months set of data. my text field [yr] and [mth] >will determine the year & month. Sample of data is as follow > >[yr] [mth] [acno] [amt] >2010 01 123 100.00 >2010 02 321 200.00 >2010 02 123 150.00 > >What i want to achieve here is how to tag the latest month as "Latest Mth" >and the month before latest month as "Previous Month". > >My new query/rpt will be > >[yr] [mth] [acno] [amt] [tag] >2010 01 123 100.00 "Previous Month" >2010 02 321 200.00 "Latest Mth" >2010 02 123 150.00 "Latest Mth" > >Please note that next month i will have different set of month data where >2010-02 & 2010-03. > >Thanks > >
From: John W. Vinson on 9 Apr 2010 13:57 On Fri, 9 Apr 2010 02:43:02 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >In my table i will have two months set of data. my text field [yr] and [mth] >will determine the year & month. Sample of data is as follow > >[yr] [mth] [acno] [amt] >2010 01 123 100.00 >2010 02 321 200.00 >2010 02 123 150.00 > >What i want to achieve here is how to tag the latest month as "Latest Mth" >and the month before latest month as "Previous Month". > >My new query/rpt will be > >[yr] [mth] [acno] [amt] [tag] >2010 01 123 100.00 "Previous Month" >2010 02 321 200.00 "Latest Mth" >2010 02 123 150.00 "Latest Mth" > >Please note that next month i will have different set of month data where >2010-02 & 2010-03. > >Thanks > > Well, as Tom suggests, this might be a lot easier with a date/time field; but you could use a calculated field such as: Tag: IIF([yr] = Year(Date()) AND Mth = Format(Date(), "mm"), "Latest Mth", IIF([Yr] = Format(DateAdd("m", -1, Date()), "yyyy") AND [mth] = Format(DateAdd("m", -1, Date()), "mm"), "Previous Month", Null)) -- John W. Vinson [MVP]
From: pink on 10 Apr 2010 21:39 "zyus" <zyus(a)discussions.microsoft.com> wrote in message news:B415CFF7-CA9E-4D8D-A0CE-85986A56035F(a)microsoft.com... > In my table i will have two months set of data. my text field [yr] and > [mth] > will determine the year & month. Sample of data is as follow > > [yr] [mth] [acno] [amt] > 2010 01 123 100.00 > 2010 02 321 200.00 > 2010 02 123 150.00 > > What i want to achieve here is how to tag the latest month as "Latest Mth" > and the month before latest month as "Previous Month". > > My new query/rpt will be > > [yr] [mth] [acno] [amt] [tag] > 2010 01 123 100.00 "Previous Month" > 2010 02 321 200.00 "Latest Mth" > 2010 02 123 150.00 "Latest Mth" > > Please note that next month i will have different set of month data where > 2010-02 & 2010-03. > > Thanks > >
From: zyus on 11 Apr 2010 21:02
Hi John, When i run the query, the tag field produced null. Appreciate if your advise. My actual mth field is month and my month text records are without any leading zero (eg 1 and not 01) SELECT Qunionall.YR, Qunionall.MONTH, IIf([Qunionall].[yr]=Year(Date()) And [Qunionall].[Month]=Format(Date(),"mm"),"Latest Mth",IIf([Qunionall].[Yr]=Format(DateAdd("m",-1,Date()),"yyyy") And [Qunionall].[month]=Format(DateAdd("m",-1,Date()),"mm"),"Previous Month",Null)) AS Tag FROM Qunionall; "John W. Vinson" wrote: > On Fri, 9 Apr 2010 02:43:02 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >In my table i will have two months set of data. my text field [yr] and [mth] > >will determine the year & month. Sample of data is as follow > > > >[yr] [mth] [acno] [amt] > >2010 01 123 100.00 > >2010 02 321 200.00 > >2010 02 123 150.00 > > > >What i want to achieve here is how to tag the latest month as "Latest Mth" > >and the month before latest month as "Previous Month". > > > >My new query/rpt will be > > > >[yr] [mth] [acno] [amt] [tag] > >2010 01 123 100.00 "Previous Month" > >2010 02 321 200.00 "Latest Mth" > >2010 02 123 150.00 "Latest Mth" > > > >Please note that next month i will have different set of month data where > >2010-02 & 2010-03. > > > >Thanks > > > > > > Well, as Tom suggests, this might be a lot easier with a date/time field; but > you could use a calculated field such as: > > Tag: IIF([yr] = Year(Date()) AND Mth = Format(Date(), "mm"), "Latest Mth", > IIF([Yr] = Format(DateAdd("m", -1, Date()), "yyyy") AND [mth] = > Format(DateAdd("m", -1, Date()), "mm"), "Previous Month", Null)) > > > -- > > John W. Vinson [MVP] > . > |