Switch statement restrictions?

[Tezdread]

Hi all,

I have been working on a query now for about a month (you may have seen previous posts), different people have offered various solutions but so far none of these have worked.

The closest I've got is with a Switch statement created in SQL view. The one below works ok:

SELECT Switch(([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]>=15 And [Income]<=32,"7000",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=40,"8500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=50,"10500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=60,"12500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=65,"13500",([RiskBand]="RB3") And [Income]>=15 And [Income]<=25,"9000",([RiskBand]="RB3") And [Income]=32,"9500",([RiskBand]="RB3") And [Income]=40,"11500",([RiskBand]="RB3") And [Income]=50,"14500",([RiskBand]="RB3") And [Income]<=60 And [Income]>=65,"16000",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]>=15 And [Income]>=17,"9000",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]>=25 And [Income]<=32,"13500",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]=40,"16500",True,"Declined") AS MaxLimit, qryRiskBand.Income
FROM qryRiskBand;

-------------------------------------------------------------

But then when I add this near the end:

([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]<=60 And [Income]>=65,"2000"

The query doesn't work and I get an error "Expression too complex in query expression"

This is the full code that's not working

SELECT Switch(([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]>=15 And [Income]<=32,"7000",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=40,"8500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=50,"10500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=60,"12500",([RiskBand]="RB5" Or [RiskBand]="RB4") And [Income]=65,"13500",([RiskBand]="RB3") And [Income]>=15 And [Income]<=25,"9000",([RiskBand]="RB3") And [Income]=32,"9500",([RiskBand]="RB3") And [Income]=40,"11500",([RiskBand]="RB3") And [Income]=50,"14500",([RiskBand]="RB3") And [Income]<=60 And [Income]>=65,"16000",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]>=15 And [Income]>=17,"9000",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]>=25 And [Income]<=32,"13500",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]=40,"16500",([RiskBand]="RB2" Or [RiskBand]="RB1") And [Income]<=60 And [Income]>=65,"2000",True,"Declined") AS MaxLimit, qryRiskBand.Income
FROM qryRiskBand;
--------------------------------------------------------

Can someone please help? If there are restrictions on how much info I can have in a query using the Switch statement is there a way to split the query so two run as one?

 

[KenHigg]

I would send all this out to a function.

Something like:

MyRiskFunction(t_Income as intger, t_RiskBand as string) as String
if t_RiskBand="RB5" Or t_RiskBand="RB4" then
	Select case t_Income 
		Case Between 15 and 32
			myRiskFunction = "7000"
		Case = 40 
			myRiskFunction = "8500"
		Case = 50 
			myRiskFunction = "10500"
		Case = 60 
			myRiskFunction = "12500"
		Case = 65 
			myRiskFunction = "13500"
	End Select		
Else if t_RiskBand="RB3" Then
	Select Case t_income
		Case Between 15 and 25
			myRiskFunction = "9000"
		Case = 32 
			myRiskFunction = "9500"
		Case = 40 
			myRiskFunction = "11500"
		Case = 50 
			myRiskFunction = "14500"
		Case between 60 and 65 
			myRiskFunction = "16000"
	End Select		
Else if t_RiskBand="RB2" or t_RiskBand="RB1"  Then
	Select Case t_income
		Case Between 15 and 17
			myRiskFunction = "9000"
		Case Between 25 and 32
			myRiskFunction = "13500"
		Case = 40
			myRiskFunction = "16500"
	End Select		
Else myRiskFunction = "Declined"
End If	
End Function

Ken

 

[Tezdread]

Thanks Ken,

It's crazy how these things go round in circles, or is that just my head?
(http://www.access-programmers.co.uk/forums/showthread.php?t=81403)

I'll start on this new method, but are you 100% sure it will work?

 

[KenHigg]

I thought I'd seen this before -

From what I been reading it should work fine...

Keep me posted.

Ken

 

[KenHigg]

Actually the following code is a little more correct:

Public Function MyRiskFunction(t_Income As Integer, t_RiskBand As String) As String
If t_RiskBand = "RB5" Or t_RiskBand = "RB4" Then
    Select Case t_Income
        Case 15 To 32
            MyRiskFunction = "7000"
        Case Is = 40
            MyRiskFunction = "8500"
        Case Is = 50
            MyRiskFunction = "10500"
        Case Is = 60
            MyRiskFunction = "12500"
        Case Is = 65
            MyRiskFunction = "13500"
    End Select
ElseIf t_RiskBand = "RB3" Then
    Select Case t_Income
        Case 15 To 25
            MyRiskFunction = "9000"
        Case Is = 32
            MyRiskFunction = "9500"
        Case Is = 40
            MyRiskFunction = "11500"
        Case Is = 50
            MyRiskFunction = "14500"
        Case 60 To 65
            MyRiskFunction = "16000"
    End Select
ElseIf t_RiskBand = "RB2" Or t_RiskBand = "RB1" Then
    Select Case t_Income
        Case 15 To 17
            MyRiskFunction = "9000"
        Case 25 To 32
            MyRiskFunction = "13500"
        Case Is = 40
            MyRiskFunction = "16500"
    End Select
Else: MyRiskFunction = "Declined"
End If

 

[Tezdread]

cheers ken, I was trying to get the other one to work but getting errors and could see part of it was around the Between part so nice one.

The code appears to be working ok, I'm not getting any compile errors but when I run the query I get #Error in all the fields. I'm guessing it's the way I have the queries setup? This is what I have

qryOvertuned
This has a working IIF statement that pulls data from tblCChecks, one of the fields has Overturned: IIF(etc

qryRiskBand
This uses the above query and pulls in Income and has another IIF nested query that is working and providing the correct results.

I made some modifications to the second code you posted but it didn't help.

I don't know what the t_ is for so as I don't have this anywhere in the db I removed them, I also tried changing the first line from String (at the end) to Integer. None of these changes seemed to make any difference.

The third query I have is

qryMaxLimit_mod
It has qryRiskBand as the query and the first field: MaxLimit: MyRiskFunction([RiskBand],[Income])

Any ideas on why it's returning the #Error?

 

[KenHigg]

Sorry, Here's how you could call it:

Given you use it in a query where you have flds name 'riskband' and 'income'

RiskFunctionResult: MyRiskFunction([income],[riskband])

 

[Tezdread]

most odd,

When I included that into the query I and run it I get this error:

Compile error:

Only comments may appear after End Sub, End Function, End Property

It opens the module and highlights the first if line RiskBand

 

[KenHigg]

Ok. I'm back. Did you ever figure out that last error?

 

[Tezdread]

not sure what was happening, I tried to quit VB when I got the compile error, and was propmted about stopping debugging and after doing that for each returned record, the query just showed it as #Error again?

I've double checked all the data and there's nothing wrong with it as far as I can tell. E.g a record has RB2 with an Income of 40 so this should result in 16500.

Now when I run the query I don't get the compile error but the results still show #Error. Would there be any chance of emailing you the DB for you to see what's going on? I can't post it here in its current state.

 

[KenHigg]

I don't have time at the moment, but when I get back I'll put together a small db and post it so you can better see how I envisioned using the function code I posted...

 

[Tezdread]

hi ken do you have time to help me finish this query

 

[Tezdread]

ah! really sorry for the multi post

Pat the query's posted up here and in another post linked from this one, sorry I'm just keen to get this query (and the stress) over

 

[Tezdread]

Pat, when I said there was another post, I don't mean it's the same...same problem for sure but I've been advised to do so many things that don't work, a three page thread isn't something people want to get in on.

I've explained the issue in these threads so if someone does want to help the best thing they could do is read the posts.

I've been trying to get a resolution to this issue for nearly 2 months...Just one query!!

I really do appreciate the help that people have offered but it's very frustrating when someone offers to help, leads you down a path that you've never been down before (and one that is confusing) and then when you get stuck and the suggestions don't work, the person that was helping, can't help anymore.

 

[FoFa]

The last function Ken posted had a colon ) right after the ELSE

Else: MyRiskFunction = "Declined"
End If

If that is still in there that would cause a compile error on the function.