Nested IIF option?

[Neo-fite]

I am using Access 2007 and in one of my queries, I have a lengthy nested IIF statement where I "Group" records based on the [Cost] Field...can this portion be shortened/condensed with another function I'm not aware of?

[FONT=&quot]

IIf([Cost]<0,"Proceeds Recv'd",
IIf([Cost]=0,"$0",
IIf([Cost]<5000,"$1-$4,999",
IIf([Cost]<10000,"$5,000-$9,999",
IIf([Cost]<25000,"$10,000-$24,999",
IIf([Cost]<50000,"$25,000-$49,999",
IIf([Cost]<75000,"$50,000-$74,999",
IIf([Cost]<100000,"$75,000-$99,999",
IIf([Cost]<150000,"$100,000-$149,999",
IIf([Cost]<250000,"$150,000-$249,999",
IIf([Cost]>=250000,"$250,000+","Blank"))))))))))) 
AS [Cost Range]

[/FONT]

 

[plog]

Yes, a custom function you build in a Module. Paste this into a module and expand on it to fit your needs:

Function getCostRange(c)
    ' takes a cost and returns the range it falls in
 
ret="Proceeds Rec'vd"
    ' holds data that will be returned, default value is returned if c<0  
 
If (c=0) Then ret="$0"
If (c>0) AND (c<5000) Then ret="$1 - $4999"
If (c>=5000) AND (c<10000) Then ret="$5000 - $9999"
 
getCostRange=ret
    ' returns cost range
 
 
End Function

Then to use it in a query you would do this:

CostRange: getCostRange([Cost])

 

[Neo-fite]

I have an excel pivot table that uses the Access Data/Query as it's source. So with this change, I get a connection error that Excel can't get to that function.

Is there a way around this? Or is it best to go back to the Nested IIF?

 

[plog]

I'm not that familiar with connecting to Access via Excel. Could you put that function in Excel?

 

[Neo-fite]

I'm not that familiar with connecting to Access via Excel. Could you put that function in Excel?

It's not really a function, it involves using excel and creating a connection via the Data Ribbon.

 

[nanscombe]

You should be able to use the Access Switch() function. (Yes, I have tried it.)

Cost Range: Switch([Cost]=0,"$0",[Cost]<5000,"$1-$4999",
[Cost]<10000,"$5000-$9999",[Cost]<25000,"$10000-$24999",
[Cost]<50000,"$25,000-$49,999",[Cost]<75000,"$50,000-$74,999",
[Cost]<100000,"$75,000-$99,999",[Cost]<150000,"$100,000-$149,999",
[Cost]<250000,"$150,000-$249,999",[Cost]>=250000,"$250,000+")

You can probably see more about Switch() in the Access Help.

As I said, I've tried creating a data connection from Excel to an Access query and this appears to work.