Formula giving me fits.....

[GMFTM]

Days:=IIF(ISBLANK[VEH_IDENT_NBR],"",CHOOSE(MIN(6,MAX(1,INT([GM_DAYS-1]/15))),"30 and Under","31-45","46-60","61-75","76-90","91+"))

So I am referencing a few columns here, and to me it looks right but I keep getting an error. Any help is appreciated as I am late shooting a report out..

 

[Special-K99]

In words, what are you trying to do?

 

[GMFTM]

I have a column with values that range from 0 - +91. I am grouping them for purposes of a pivot table. that is hte "30 and Under", "31-45"...etc.

I had the formula in excel and it worked fine, when I put in in access I am having the issues. I figured it would be transparent...

I don't have to check for the VEH_IDENT_NBR, but it is a good checksum for me.

 

[Special-K99]

Your table is called "GM-DAYS-1" or are you subtracting 1 from GM-DAYS ?

Try baseing something on this:

=IF(ISBLANK[VEH_IDENT_NBR],"",LOOKUP(INT([GM_DAYS-1]),{0,31,46,61,76,91},{"30 and Under","31-45","46-60","61-75","76-90","91+"})
)

 

[GMFTM]

GM_DAYS is the column, in excel I did -1 to get it to work. So I assume it would be like this:

=IF(ISBLANK[VEH_IDENT_NBR],"",LOOKUP(INT([GM_DAYS]),{0,31,46,61,76,91},{"30 and Under","31-45","46-60","61-75","76-90","91+"})
)

However when I try that, it gives me an error. "The expression you entered contains invalid syntax" "You may have entered an operand without an operator"

Your table is called "GM-DAYS-1" or are you subtracting 1 from GM-DAYS ?

Try baseing something on this:

=IF(ISBLANK[VEH_IDENT_NBR],"",LOOKUP(INT([GM_DAYS-1]),{0,31,46,61,76,91},{"30 and Under","31-45","46-60","61-75","76-90","91+"})
)

 

[james_lankford]

try the Excel forum
this is the Access forum