Looking for Expression using date conditions.

**PC Datasheet** · May 26 '06, 02:25 AM

Re: Looking for Expression using date conditions.

I am not an MVP and you should not blindly rely on MVPs!

I will assume from your problem statement that the "day" portion of the
Date in the temp column will be the same as the "day" portion of
Date_Entered.

MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
DateAdd("m",1,( Date_Entered])

You can copy and paste this expression into your query to test if it gives
you what you want. If it does not, explain why and I will give you another
expression based on your reason why it does not.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
resource@pcdata sheet.com

"Mr.Kane" <kane.marc@gmai l.com> wrote in message
news:1148597195 .224329.79740@u 72g2000cwu.goog legroups.com...[color=blue]
> Here's my dilemma. I am putting together a trend report using
> PivotCharts and so the query that I am trying to construct basically
> would look at the "Date_Enter ed" for a record and if the "day" portion
> of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp
> column with the actual month and year.
>
> However if the "day" portion of the Date is > 15 (ie 1/16/2006 -
> 1/31/2006) it will populate a temp column with the following month
> (actual month + 1) and year.
>
> This the is the current expression as I have it constructed:
>
> MonthOpened: IIf(Month([date_entered])>9 And
> Day([date_entered])<=15,Year([date_entered]) &
> Month([date_entered]),Year([date_entered]) & '0' &
> Month([date_entered])) Or IIf(Month([date_entered])>9 And
> Day([date_entered])>15,Year([date_entered]) & Month([date_entered]) +
> 1,Year([date_entered]) & '0' & Month([date_entered]) + 1)
>
> Anhy guidance that I can get from any of you Access MVPs woud be
> extremely welcome.
>
>
> Thanks
> Marc
>[/color]

**Please Stop Advertising** · May 26 '06, 03:55 AM

Re: Looking for Expression using date conditions.

* PC Datasheet:[color=blue]
> I am not an MVP and you should not blindly rely on MVPs!
>
> I will assume from your problem statement that the "day" portion of the
> Date in the temp column will be the same as the "day" portion of
> Date_Entered.
>
> MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
> DateAdd("m",1,( Date_Entered])
>
> You can copy and paste this expression into your query to test if it gives
> you what you want. If it does not, explain why and I will give you another
> expression based on your reason why it does not.
>
>[/color]

--
To anyone reading this thread:

It is commonly accepted that these newsgroups are for free
exchange of information. Please be aware that PC Datasheet
is a notorious job hunter. If you are considering doing
business with him then I suggest that you take a look at
the link below first.

http://home.tiscali.nl/arracom/whoissteve.html

Randy Harris

**John Marshall, MVP** · May 26 '06, 12:05 PM

Re: Looking for Expression using date conditions.

"PC Datasheet" <NoSpam@Spam.Co m> wrote in message
news:xCtdg.288$ UT2.42@newsread 3.news.pas.eart hlink.net...[color=blue]
>I am not an MVP[/color]

and with the way you behave and the amount of wrong information you spout,
it is very unlikely you will be.
[color=blue]
>and you should not blindly rely on MVPs![/color]

They have far more credibility than you will ever attain. The MVP award is
an annual award from Microsoft that is given to individuals for their
product knowledge and FREE community support. Most of the current Access
MVPs have recieved an annual MVP award since they were first awarded.

John... Visio MVP

**pietlinden@hotmail.com** · May 26 '06, 05:05 PM

Re: Looking for Expression using date conditions.

Oh, so you mean *advertising* in your posts will pretty much cause you
never to be given an MVP award????!!!!

(tongue in cheek, of course!)

**Mr.Kane** · May 26 '06, 08:55 PM

Re: Looking for Expression using date conditions.

The following expression did work,

MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
DateAdd("m",1,( Date_Entered])

however I need the output to be in (YYYYMM) format, that's why my
expression:

MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) &
Month([date_entered]),Year([date_entered]) & '0' &
Month([date_entered]))

was constructed that way. How can I add the condition (IIF
Day(date_entere d)>15, then Year([date_entered]) &
Month([date_entered])+1 to my expression?

Thanks again and I apologize for being picky...

**Bob Quintal** · May 26 '06, 10:35 PM

Re: Looking for Expression using date conditions.

"Mr.Kane" <kane.marc@gmai l.com> wrote in
news:1148597195 .224329.79740@u 72g2000cwu.goog legroups.com:
[color=blue]
> Here's my dilemma. I am putting together a trend report using
> PivotCharts and so the query that I am trying to construct
> basically would look at the "Date_Enter ed" for a record and if
> the "day" portion of the Date is <= 15 (ie 1/1/2006 -
> 1/15/2006) it will populate a temp column with the actual
> month and year.
>
> However if the "day" portion of the Date is > 15 (ie 1/16/2006
> - 1/31/2006) it will populate a temp column with the following
> month (actual month + 1) and year.
>
> This the is the current expression as I have it constructed:
>
> MonthOpened: IIf(Month([date_entered])>9 And
> Day([date_entered])<=15,Year([date_entered]) &
> Month([date_entered]),Year([date_entered]) & '0' &
> Month([date_entered])) Or IIf(Month([date_entered])>9 And
> Day([date_entered])>15,Year([date_entered]) &
> Month([date_entered]) + 1,Year([date_entered]) & '0' &
> Month([date_entered]) + 1)
>
> Anhy guidance that I can get from any of you Access MVPs woud
> be extremely welcome.
>
>
> Thanks
> Marc
>[/color]
First, if you put the year first, you will be able to sort
properly. You code fails to correctly handle dates after Dec 15,
which should fall into the next year.
Working with numbers instead of strings is an advantage in a
case like this, you don't have to worry about zeroes for months
1-9.

MonthOpened: Year([date_entered]*100+
IIF(month([date_entered])=12 and day([date_entered])>15,1,0)
+Month([date_entered])+iif(day([date_entered])>15,1,0)

200606 for today. store as a long integer or convert to a string

--
Bob Quintal

PA is y I've altered my email address.

**Bob Quintal** · May 26 '06, 10:45 PM

Re: Looking for Expression using date conditions.

"Mr.Kane" <kane.marc@gmai l.com> wrote in
news:1148677032 .261058.11060@3 8g2000cwa.googl egroups.com:
[color=blue]
> The following expression did work,
>
> MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
> DateAdd("m",1,( Date_Entered])
>
> however I need the output to be in (YYYYMM) format, that's why[/color]
my[color=blue]
> expression:
>
> MonthOpened: IIf(Month([date_entered])>9,Year([date_entered])[/color]
&[color=blue]
> Month([date_entered]),Year([date_entered]) & '0' &
> Month([date_entered]))
>
> was constructed that way. How can I add the condition (IIF
> Day(date_entere d)>15, then Year([date_entered]) &
> Month([date_entered])+1 to my expression?
>
>
> Thanks again and I apologize for being picky...
>[/color]
Don't apologise to PCD, you are entitled to a correct response.
The man hasn't furnished a correct, relevant answer in the
several years I'v been reading this group.
see my separate response to your question for a solution.

--
Bob Quintal

PA is y I've altered my email address.

**Bob Quintal** · May 26 '06, 10:55 PM

Re: Looking for Expression using date conditions.

Bob Quintal <rquintal@sympa tico.ca> wrote in
news:Xns97CFBAA 5210C0BQuintal@ 207.35.177.135:
[color=blue]
> MonthOpened: Year([date_entered]*100+
> IIF(month([date_entered])=12 and day([date_entered])>15,1,0)
> +Month([date_entered])+iif(day([date_entered])>15,1,0)
>
> 200606 for today. store as a long integer or convert to a string
>[/color]
me bad! I forgot to add some parentheses and to fix month 13.
Remind me to debug first, post after.

MonthOpened: Year([date_entered])*100+
IIf(Month([date_entered])=12
And Day([date_entered])>15,100,0)
+ (Month([date_entered])
+ IIf(Day([date_entered])>15,1,0)) Mod 12

--
Bob Quintal

PA is y I've altered my email address.

**PC Datasheet** · May 26 '06, 10:55 PM

Re: Looking for Expression using date conditions.

Change to this:

MonthOpened:For mat((IIF(Day([Date_Entered]) <= 15, [Date_Entered],
DateAdd("m",1,( Date_Entered])),"yyyymm")

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
resource@pcdata sheet.com

"Mr.Kane" <kane.marc@gmai l.com> wrote in message
news:1148677032 .261058.11060@3 8g2000cwa.googl egroups.com...[color=blue]
> The following expression did work,
>
> MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
> DateAdd("m",1,( Date_Entered])
>
> however I need the output to be in (YYYYMM) format, that's why my
> expression:
>
> MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) &
> Month([date_entered]),Year([date_entered]) & '0' &
> Month([date_entered]))
>
> was constructed that way. How can I add the condition (IIF
> Day(date_entere d)>15, then Year([date_entered]) &
> Month([date_entered])+1 to my expression?
>
>
> Thanks again and I apologize for being picky...
>[/color]

**Steve** · May 27 '06, 02:35 AM

Re: Looking for Expression using date conditions.

Did the new expression I gave you give you what you wanted?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
resource@pcdata sheet.com

"Mr.Kane" <kane.marc@gmai l.com> wrote in message
news:1148677032 .261058.11060@3 8g2000cwa.googl egroups.com...[color=blue]
> The following expression did work,
>
> MonthOpened:IIF (Day([Date_Entered]) <= 15, [Date_Entered],
> DateAdd("m",1,( Date_Entered])
>
> however I need the output to be in (YYYYMM) format, that's why my
> expression:
>
> MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) &
> Month([date_entered]),Year([date_entered]) & '0' &
> Month([date_entered]))
>
> was constructed that way. How can I add the condition (IIF
> Day(date_entere d)>15, then Year([date_entered]) &
> Month([date_entered])+1 to my expression?
>
>
> Thanks again and I apologize for being picky...
>[/color]

**Mr.Kane** · May 30 '06, 04:55 PM

Re: Looking for Expression using date conditions.

Bob,

Thank you for the mod to Steve's original expression.

**Keith Wilby** · May 31 '06, 08:05 AM

Re: Looking for Expression using date conditions.

"Mr.Kane" <kane.marc@gmai l.com> wrote in message
news:1149007840 .521083.81000@j 73g2000cwa.goog legroups.com...[color=blue]
> Bob,
>
> Thank you for the mod to Steve's original expression.
>[/color]

Praise and put-down in the same phrase, cool.

Keith.

**Mr.Kane** · May 31 '06, 11:15 PM

Re: Looking for Expression using date conditions.

Keith Wilby wrote:[color=blue]
> "Mr.Kane" <kane.marc@gmai l.com> wrote in message
> news:1149007840 .521083.81000@j 73g2000cwa.goog legroups.com...[color=green]
> > Bob,
> >
> > Thank you for the mod to Steve's original expression.
> >[/color]
>
> Praise and put-down in the same phrase, cool.
>
> Keith.[/color]

I wasn't trying to insult anyone I just used Bob's modded expression
and was thanking him for his time. I appreciate Steve taking the time
to answer my question as well. I'm not interested in jumping into the
fray here.

**Mr.Kane** · May 31 '06, 11:25 PM

Re: Looking for Expression using date conditions.

Everything looks good, however it seems that the records from Dec 2004
with dates less or equal to the 15th are defaulting to "YYYY00"

ie
record date entered 12/5/2004 = "200400"
record date entered 12/11/2005 = "200500"
record date entered 12/2/2004 = "200400"

This the the active expression being used:
MonthOpened: Year([date_entered])*100+IIf(Month ([date_entered])=12 And
Day([date_entered])>15,100,0)+(Mo nth([date_entered])+IIf(Day([date_entered])>15,1,0))
Mod 12

any additonal help would be appreciated

(I'll try and tweak the expression and see if I can resolve the
conflict as well)

Looking for Expression using date conditions.

Looking for Expression using date conditions.

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment