date format

[venu_resoju]

Hai Friends...

I have text box on my report, in which I want to display as "01-Apr-2015" for first half of the financial year.for this I have given a code in the text box is

="021"&"-"& IIf((Format(Now(),"mmm")="Apr",Format(Now(),"mmm")="May",Format(Now(),"mmm")="Jun",Format(Now(),"mmm")="Jul",Format(Now(),"mmm")="Aug",Format(Now(),"mmm")="Sep"),"Oct","Apr")&"-"&"2015"

but it is not working
but

=IIf((Format(Now(),"mmm")="Apr",Format(Now(),"mmm")="May",Format(Now(),"mmm")="Jun",Format(Now(),"mmm")="Jul",Format(Now(),"mmm")="Aug",Format(Now(),"mmm")="Sep"),"Oct","Apr")

it results "Apr"

how can I farm this
please help me as I wanted result.
I am requiting you all
thanking you.

 

[pr2-eugin]

What is that you are exactly trying to do? Could you explain that in simple words?

 

[vbaInet]

Confused as well!

You want to return the 1st of April for the current year in the format "01-Apr-2015"?

"01-Apr-" & Year(Date())

 

[venu_resoju]

Thank you my dear friends,

I am trying to get.....

If current month is Apr to Sep ( Apr or May or Jun or Jul or Aug or Sep), I want to get the text box result is "01-Apr-current year".

If current month is Oct to Dec ( Oct or Nov or Dec), I want to get the text box result is "01-Oct-current year".

If current month is Jan to Mar ( Jan or Feb or Mar), I want to get the text box result is "01-Oct-(current year-1)".

I want to get these results for a text box on my ESIC form-37.

how can I get this, please help me to solve this.

Thanking you all

 

[namliam]

Lets first format your IIF

="021"&"-"& IIf((Format(Now(),"mmm")="Apr"
                ,Format(Now(),"mmm") ="May"
                ,Format(Now(),"mmm")="Jun"
                ,Format(Now(),"mmm ")="Jul"
                ,Format(Now(),"mmm")="Aug"
                ,Format(Now(),"m mm")="Sep"),"Oct","Apr")&"-"&"2015"

For this to "somewhat" work, you would want to use OR, not use the comma notation you have.

="021"&"-"& IIf((Format(Now(),"mmm")="Apr"
              or Format(Now(),"mmm") ="May"
              or Format(Now(),"mmm")="Jun"
              or Format(Now(),"mmm ")="Jul"
              or Format(Now(),"mmm")="Aug"
              or Format(Now(),"m mm")="Sep"),"Oct","Apr")&"-"&"2015"

I will let you fix your own typo's on top of that I would not use strings rather use i.e. month to get a number, which works so much more simple

IIF( Month(date()) >= 4 
 and Month(date()) <= 9, ....

The easiest way overall to do this... depends on how often will you use this.... or simular "awkward" date functions like fiscal quarter or fiscal month.

If you only have this year thing to do and if its used in a limited amount of places... lets say only in 1 or max say 3 places.... inline function will work just fine... otherwize you may want to put this into a function.
Inline:

IIF( Month(Dateadd("M", -3, Date())) <= 6, "01-Apr-"
                                         , "01-Oct-") & Year (Dateadd("M", -3, Date()))

Re-writing this to a proper function shouldnt be too hard, so I will leave that (for now) up to you.

Also you may want to tweak this so that you dont have a TEXT string, but rather have a "proper" date, working with text instead of dates can cause havoc down the line.

If you have multiple fiscal things to work with a date dimension table will work best. A table that contains all dates from say 01-01-2000 thru 31-12-2050 (or some date in the future) and the appropriate values you need for fiscal year logic as well as (for example) workday logic that you may need.

 

[venu_resoju]

Thank you my friend namliam...and all.....Thank you...

wonderful... it is working...

I can't express in my words..I am very happy..

I have been trying to get this for a very long time, but now I got it...
I am always thankful to you my friend...

Thanking you..