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: John W. Vinson on 11 Apr 2010 22:27 On Sun, 11 Apr 2010 18:02:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >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) > You realize that Month (and Year) are reserved words and will cause Access to get confused? Is there some particular reason that you posted incorrect fieldnames and data contents when we requested your data structure, and then complained when we used them and they didn't work? Sigh... Ok, ASSUMING - because you haven't said - that the fields are Number fields named Month and YR, and that you're lucky enough that the reserved words don't cause errors (a risky assumption since I'll need to use the builtin Month function in the query), try SELECT Qunionall.YR, Qunionall.[MONTH], IIf([yr]=Year(Date()) And [Month]=Month(Date()), "Latest Mth", IIf([Yr]=Year(DateAdd("m",-1,Date())) And [month]=Month(DateAdd("m",-1,Date())),"Previous Month",Null)) AS Tag FROM Qunionall; -- John W. Vinson [MVP]
From: zyus on 12 Apr 2010 03:09 Dear John Sorry my mistake on the field names & data contents. Purely overlooked. "To complain" not at all as i know i pay nothing to you. Please bear with me on my lack of english writing skill, access knowledge and short of jargon that normally used by computer expert as i consider myself still new to access. As i really appreciated your response sometimes i feel that i'm also hit by a tinge of sarcasm. "John W. Vinson" wrote: > On Sun, 11 Apr 2010 18:02:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >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) > > > > You realize that Month (and Year) are reserved words and will cause Access to > get confused? > > Is there some particular reason that you posted incorrect fieldnames and data > contents when we requested your data structure, and then complained when we > used them and they didn't work? > > Sigh... > > Ok, ASSUMING - because you haven't said - that the fields are Number fields > named Month and YR, and that you're lucky enough that the reserved words don't > cause errors (a risky assumption since I'll need to use the builtin Month > function in the query), try > > SELECT Qunionall.YR, Qunionall.[MONTH], > IIf([yr]=Year(Date()) And [Month]=Month(Date()), "Latest Mth", > IIf([Yr]=Year(DateAdd("m",-1,Date())) And > [month]=Month(DateAdd("m",-1,Date())),"Previous Month",Null)) AS Tag > FROM Qunionall; > > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 12 Apr 2010 13:45 On Mon, 12 Apr 2010 00:09:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Dear John > >Sorry my mistake on the field names & data contents. Purely overlooked. >"To complain" not at all as i know i pay nothing to you. > >Please bear with me on my lack of english writing skill, access knowledge >and short of jargon that normally used by computer expert as i consider >myself still new to access. > >As i really appreciated your response sometimes i feel that i'm also hit by >a tinge of sarcasm. I apologize, Zyus. There was far more than a trace of sarcasm; it was inappropriate and I should not have been posting at all when I was in such a mood. I hope your query is now working; if not, please post back and I'll try to help. -- John W. Vinson [MVP]
From: zyus on 12 Apr 2010 20:37 Thanks for your response. Tried the query and successfully tagged "previous month" but not the "latest month" which is still remained null. "John W. Vinson" wrote: > On Mon, 12 Apr 2010 00:09:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >Dear John > > > >Sorry my mistake on the field names & data contents. Purely overlooked. > >"To complain" not at all as i know i pay nothing to you. > > > >Please bear with me on my lack of english writing skill, access knowledge > >and short of jargon that normally used by computer expert as i consider > >myself still new to access. > > > >As i really appreciated your response sometimes i feel that i'm also hit by > >a tinge of sarcasm. > > I apologize, Zyus. There was far more than a trace of sarcasm; it was > inappropriate and I should not have been posting at all when I was in such a > mood. > > I hope your query is now working; if not, please post back and I'll try to > help. > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 12 Apr 2010 23:02
On Mon, 12 Apr 2010 17:37:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Thanks for your response. > >Tried the query and successfully tagged "previous month" but not the "latest >month" which is still remained null. Hrm. Please post your current query and some sample data including records from April 2010. If the previous works, I don't see why the current month shouldn't - it's a simpler query! -- John W. Vinson [MVP] |