Update query. How many iif statements are allowed?

[bsnapool]

Hi,

I am going to be creating a iif statement update qery, to update a column on dates, E.g. would be....

IIf([FinMonth] between 01/01/2008 and 31/01/2008,1)iif([FinMonth] between 01/02/2008 and 30/02/2008,2)iif(etc etc

2 questions??

1) Is 12 nested iif's statements are allowed within one IIF statement?
2) iS THERE AN EASIER WAY TO DO THIS??

Hope this explains enough.

Thanks

 

[KenHigg]

Write a function.

 

[bsnapool]

Function. I am not the best with vba. Is there another alternative?

 

[KenHigg]

To start with it looks like you could use a datepart() function to extract the month from the date. Second, you could derive this where-ever you need it so I don't see any real need to have a field for it.

Functions are easy, and very usefull. I think there are samples in the sample database section.

???

 

[bsnapool]

KEN,

Thanks very much for your help will look into what you have suggested.

 

[KenHigg]

Cool - post back if you get stuck -

 

[bsnapool]

Ken,

The datepart() function worked a treat but what if Im wanting to do it from the financial year? April = 1, May = 2

Any ideas?

 

[KenHigg]

Could you try to subtract 4 from what datediff() returns?

???

 

[KenHigg]

Never mind, that wouldn't work...

hum...

 

[bsnapool]

I have tried -3 and this seems to work but wondering whether i would come across problems when new financial years comes in?

Financial_Month (Actual Month)
DatePArt



1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
9 12
10 1
11 2
12 3

Any ideas of more dynamic approach?

 

[KenHigg]

Maybe you could tinker with the 'firstweekofyear' argument in the datepart() function:

DatePart(interval, date[,firstdayofweek[,firstweekofyear]])


???

 

[bsnapool]

Hey,

Still struggling with this. The -3 will not work as when it gets to January it will be -2??

Any ideas??

 

[KenHigg]

Yeah I realized that right after I suggested it...

Maybe I'll find a few minutes after while to write you up a simple function to do this...

 

[Brianwarnock]

After the datepart extracts the actual month use a Select Case or Switch to give the financial month.

Brian

 

[bsnapool]

Fellas,

Thanks very much your input really appreciated.

Brian,

Any examples??

 

[Brianwarnock]

If you don't want to write a function use a Switch eg
Switch(Month(fldDate)=4,1,Month(fldDate)=5,2....etc)

Brian

 

[bsnapool]

Interesting!! I have never heard of this function before.

Brian, thanks for your help(AGAIN) will have a try and post back.

 

[Brianwarnock]

Interesting!! I have never heard of this function before.
.

I came across it in this forum, it seems to have arrived after I retired, but recently Moniker used it to simplify a complex nested IIF I had done, so I decided to take a look see. It looks useful.

Brian

 

[bsnapool]

Switch Function

Worked a treat!! A lot easier that nested iif statements. Thanks Brian.

Also Thanks Ken for your efforts and help.

 

[Brianwarnock]

I guess Moniker should also be thanked for switching me onto the Switch function. ouch!

Brian