Looking for Expression using date conditions.

[Mr.Kane]

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_Entered" 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

[PC Datasheet]

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
re******@pcdatasheet.com

"Mr.Kane" <ka*******@gmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...

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_Entered" 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

[Please Stop Advertising]

* PC Datasheet:

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.

--
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]

"PC Datasheet" <No****@Spam.Com> wrote in message
news:xC**************@newsread3.news.pas.earthlink .net...

I am not an MVP
and with the way you behave and the amount of wrong information you spout,
it is very unlikely you will be.
and you should not blindly rely on MVPs!

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]

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]

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_entered)>15, then Year([date_entered]) &
Month([date_entered])+1 to my expression?
Thanks again and I apologize for being picky...

[Bob Quintal]

"Mr.Kane" <ka*******@gmail.com> wrote in
news:11*********************@u72g2000cwu.googlegro ups.com:

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_Entered" 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

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]

"Mr.Kane" <ka*******@gmail.com> wrote in
news:11*********************@38g2000cwa.googlegrou ps.com:

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_entered)>15, then Year([date_entered]) &
Month([date_entered])+1 to my expression?
Thanks again and I apologize for being picky...

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]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

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

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]

Change to this:

MonthOpened:Format((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
re******@pcdatasheet.com

"Mr.Kane" <ka*******@gmail.com> wrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...

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_entered)>15, then Year([date_entered]) &
Month([date_entered])+1 to my expression?
Thanks again and I apologize for being picky...

[Steve]

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
re******@pcdatasheet.com
"Mr.Kane" <ka*******@gmail.com> wrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...

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_entered)>15, then Year([date_entered]) &
Month([date_entered])+1 to my expression?
Thanks again and I apologize for being picky...

[Mr.Kane]

Bob,

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

[Keith Wilby]

"Mr.Kane" <ka*******@gmail.com> wrote in message
news:11*********************@j73g2000cwa.googlegro ups.com...

Bob,

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

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

Keith.

[Mr.Kane]

Keith Wilby wrote:

"Mr.Kane" <ka*******@gmail.com> wrote in message
news:11*********************@j73g2000cwa.googlegro ups.com...

Bob,

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

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

Keith.

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]

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)+(Month([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)

[Bob Quintal]

"Mr.Kane" <ka*******@gmail.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:

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"
any additonal help would be appreciated

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

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

Sorry again for insufficient testing.
--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

"Mr.Kane" <ka*******@gmail.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:

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"
any additonal help would be appreciated

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

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

Sorry again for insufficient testing.

that one has a bug too.

this seems to work right
MonthOpened: Year([calldate])*100+IIf(Month([calldate])=12 And
Day([calldate])>15,100,0)+(Month([calldate])+IIf(Day
([calldate])>15,1,0))-IIf(Month([calldate])=12 And Day
([calldate])>15,12,0)

--
Bob Quintal

PA is y I've altered my email address.

[Mr.Kane]

Thanks again Bob...