Creating date range groups using Iif and Date? My formula needs tweaking

[Southsider]

I am trying to create a calculated field that outputs what date range of the month a file was received by a department. I want to create the following groups: day of month 1-10 in one group, days 11-20, and 21-EOM. I tried to create a formula to do this and I think I messed up something simple, any help fixing this would be greatly appreciated, thanks!

UW Receipt Range: IIf(Date('d',[CDF90 DATE- CREDIT RECEIVED BY UW])<11,"00-10",IIf(Date('d',[CDF90 DATE- CREDIT RECEIVED BY UW])<21,"11-20",IIf(Date('d',[CDF90 DATE- CREDIT RECEIVED BY UW])<32,"21-EOM","NA"))))

 

[KenHigg]

A quick glance and I'm thinking you need to fix the date values, maybe '#1/1/2012#' type thing - ?

 

[Southsider]

A quick glance and I'm thinking you need to fix the date values, maybe '#1/1/2012#' type thing - ?

That could be, I probably need to put 'dd' I'm thinking, but I think there's another problem before that because Access is saying "The expression you entered has a function containing the wrong number of arguments".

 

[KenHigg]

Can you get it to work with one iif()?

 

[Southsider]

Can you get it to work with one iif()?

No, I don't know how. Sorry, I'm a big newbie at this stuff. I found a code snippet that was doing something similar and tried to adapt it to my use. I have tried a few different variations of what I posted and am still getting errors. I don't really know the proper syntax.

 

[KenHigg]

You should search for the IIF() statement and understand it then search nested IIF()s (Which is what you have). IIF()s are really simple but can be combined into monsters...

 

[KenHigg]

Try this:

UW Receipt Range: IIf(Date('d',[CDF90 DATE- CREDIT RECEIVED BY UW])<11,"00-10","NA")

 

[Southsider]

Try this:

UW Receipt Range: IIf(Date('d',[CDF90 DATE- CREDIT RECEIVED BY UW])<11,"00-10","NA")

I get how the Iif's work, it is simple. I thought I was screwing up the syntax a bit though. I just tried this code and it says "The expression you entered has a function containing the wrong number of arguments."

 

[KenHigg]

the date function is wrong - you want the date part() function

You should have have kept simplifying the code until it worked and then start putting stuff back in until it broke again

 

[Southsider]

I ended up just using the "Day" function. Here's the code that finally worked:

UW Receipt Range: IIf(Day([CDF90 DATE- CREDIT RECEIVED BY UW])<11,"1-10",IIf(Day([CDF90 DATE- CREDIT RECEIVED BY UW])<21,"11-20",IIf(Day([CDF90 DATE- CREDIT RECEIVED BY UW])<32,"21-EOM","NA")))

 

[KenHigg]

Cool - glad you have it working.