IIF/Switch: The expression you entered is too complex

[Access_Help]

Neither of these work as they are too complex for a calculated field in a query.
I am not good at VB coding, is there any other way around this????




IIF

IIf([CWGRADE]="A+",87,IIf([CWGRADE]="A",84,IIf([CWGRADE]="A-",80,IIf([CWGRADE]="B+",77,IIf([CWGRADE]="B",74,IIf([CWGRADE]="B-",70,IIf([CWGRADE]="C+",67,IIf([CWGRADE]="C",64,IIf([CWGRADE]="C-",60,IIf([CWGRADE]="D+",57,IIf([CWGRADE]="D",54,IIf([CWGRADE]="D-",50,IIf([CWGRADE]="E+",47,IIf([CWGRADE]="E",44,IIf([CWGRADE]="E-",40,IIf([CWGRADE]="F+",37,IIf([CWGRADE]="F",34,IIf([CWGRADE]="F-",30,IIf([CWGRADE]="G+",27,IIf([CWGRADE]="G",24,IIf([CWGRADE]="G-",20))))))))))))))))))))



Switch

Grade__to_Percentage: Switch([CWGRADE]="A+",87,[CWGRADE]="A",84,[CWGRADE]="A-",80,[CWGRADE]="B+",77,[CWGRADE]="B",74,[CWGRADE]="B-",70,[CWGRADE]="C+",67,[CWGRADE]="C",64,[CWGRADE]="C-",60,[CWGRADE]="D+",57,[CWGRADE]="D",54,[CWGRADE]="D-",50,[CWGRADE]="E+",47,[CWGRADE]="E",44,[CWGRADE]="E-",40,[CWGRADE]="F+",37,[CWGRADE]="F",34,[CWGRADE]="F-",30,[CWGRADE]="G+",27,[CWGRADE]="G",24,[CWGRADE]="G-",20,true,0)

 

[pr2-eugin]

There should be a better way for doing this that is 100% certain. Maybe you could edit your post that we could read it first.

 

[bmal]

I have the same situation as the OP. Here is my query expression:

File_Status: IIf([Current_Status]="Prospect","01. Prospect",IIf([current_status]="Application","02. Application",IIf([current_status]="Sent To Processing","03. Sent To Processing",IIf([current_status]="HMDA Complete","03. Sent To Processing",IIf([current_status]="Submitted","04. Submitted to UW",IIf([current_status]="Re-submitted","05. Re-Submitted to UW",IIf([current_status]="Approved","06. Approved UW",IIf([current_status]="Suspended","06. Suspended UW",IIf([current_status]="Clear to Close","07. Clear to Close",IIf([current_status]="Docs Drawn","08. In Closing",IIf([current_status]="Docs Out","08. In Closing",IIf([current_status]="Closed","09. Closed",IIf([current_status]="Denied","20. Denied",IIf([funded_date] Is Not Null,"10. Funded","Invalid"))))))))))))))

NOTE: This worked fine until I added one more IIF. I think it worked w/ 10 and broke at 11.

I think I do OK when I drive Access like a Fiat. The problem is when I try and drive it like a Ferrari, I hit all sorts of road bumps and eventually the front bumper is left on the side of the road. :banghead:

So my question is: Any general direction or guidance regarding what the next step is when the expression becomes too complex? Many thanks.

 

[bmal]

I feel guilty and need to confess that I didn't spend more than 10 minutes looking for a solution on this. Kinda jumped the gun; should have given at least 60 to finding a workaround. Sorry guys. I'm usually good about that. I think it's the 13 hour day thing catching up.

I am still curious how a wise Access sage would remedy the 'too complex' thing.

 

[Ari]

Hello!

For this case I suggest you use a function.

Something like:

Public function fncFileStatus(strStatus as string) as striing
select case strStatus
case "Application": fncFileStatus = "02. Application"
case "Sent To Processing": fncFileStatus = "03. Sent To Processing"
case "HMDA Complete": fncFileStatus = "03. Sent To Processing"
end select
end function

File_Status: fncFileStatus([current_status])

 

[CazB]

The other option is you add a table which holds the value you've got and the ones you want to display, and then use that table in a query to 'lookup' the value you want to use / display...

eg for the OP : tblCWGrades with 2 fields (or more, if you like ) I tend to add an ID field to my tables and then use that as the link rather than the text field but it should still work if you just use the text field (Grade) as the link...
Grade Percentage
A+ 87
A 84
A- 81
and so on?

Or for bmal tblFileStatus with 2 fields (or more, if you like
Status StatusDesc
Prospect 01. Prospect
Application 02. Application
Sent to Processing 03. Sent To Processing
and so on...

the advantage being you can then just add to the table if any more 'options' are added?