using a function in a query for if/then/else

[folkie]

I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.

I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function IfThen(BYStep As String)

Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer


If
BYStep = "07.0" Then
test2 = Step070

ElseIf BYStep = "07.5" Then
test2 = Step075

ElseIf BYStep = "08.0" Then
test2 = Step080

Else: test2 = 0
End If

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In my query I have:
IfThen([BYStep]) AS Test2


It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.

Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?

 

[Brianwarnock]

Can I suggest that you read VBA help on the Case function.

Brian

 

[Brianwarnock]

On the other hand would a function as simple as

Function test2(Bystep as string)

test2= "step" & (Bystep)

End Function

work if your example data is anything to go by.

Brian

 

[folkie]

Regarding the suggestion about using Case, I did this:

Public Function TryThis(BYStep As String)

Dim Step070 As Integer
Dim Step080 As Integer
Dim Step160 As Integer

Select Case BYStep
Case BYStep = "07.0"
TryThis = Step070
Case BYStep = "08.0"
TryThis = Step080
Case Else
TryThis = 4
End Select
End Function

And all I get is 4 for every record. Prior to this code, I used "Test2" instead of "TryThis" in the "TryThis = Step......." lines. Using "Test2 =" I got nulls. Using "TryThis=" I, at least, got the value in the last Case statement.

I then went back to my original if/then/else and changed where I had Test2 = Step.... to IfThen = Step.... as follows:

Public Function IfThen(BYStep As String)

Dim Step070 As Integer
Dim Step160 As Integer
Dim Step135 As Currency
Dim tSalary As TableDef

If BYStep = "16.0" Then
IfThen = Step160

ElseIf BYStep = "13.5" Then
IfThen = Step135

Else: IfThen = 6
End If

End Function

Here, for the small sample, when BYStep was 16.0 or 13.5, I got 0 - which are not the values of Step160 or Step135. For other values of BYStep, I got 6. Thus, it looks like the logic is working, but I'm not getting the true values of the Step160 or Step135 variables.

These Step... variables are fields in a table, called tSalary. Whenever I use something like tSalary.Step080 or [tSalary].[Step080] in my code, it doesn't like that.

Is there a way to get the value of, for example, tSalary.Step160 when the BYStep is "16.0"?

I'm not sure I understand the 2nd suggestion about concatenating the 2 fields.

 

[Brianwarnock]

The suggestion of concatenating the fields was because that is what you appear to require Step and the value of Bystep.

The Case statement syntax is wrong, you do not need Bystep =


Select Case BYStep
Case "07.0"
TryThis = Step070
Case "08.0"
etc

 

[folkie]

Brian,

Thanks for the correction. Now it's like the if/then/else sample. I get 0, (instead of the true value for the Step fields) for the few fields in my sample code.

I'm still don't know how to get the true values of those Step fields from the tSalary table.

 

[Brianwarnock]

OK I've gone back and read the thread again and realised I'd jumped in a bit quick. You have declared step070 etc but not given them any values. What is it that you want test2 to be if ByStep ="07.0"

Brian

 

[folkie]

Brian,

I have a table called tSalary which has 41 of these Step fields. The values I want are in that table. One of the fields is called Step070. The 42nd field in tSalary is called Grade. There are 22 records in this table - it goes from Grade 005 to 026. An employee's salary is determined by their grade and step. Thus Step070 will vary depending on what the Grade is.

In my query, I join the Grade in tSalary to the CYGrade (Current Year Grade) in the tSnapShot table. What I've been doing in 3 fields (because Access say it's too complex to put all these IIFs in 1 field) is this:

IIf(BYStep="00.0",tSalary.Step000*CYFTE,
IIf(BYStep="00.5",tSalary.Step005*CYFTE,
IIf(BYStep="01.0",tSalary.Step010*CYFTE,
IIf(BYStep="01.5",tSalary.Step015*CYFTE......... AS Salary1

I didn't use CYFTE in my previous postings to keep it a little simpler, but CYFTE is a number (usually 1, but sometimes a fraction of 1).

 

[Brianwarnock]

I'm sorry Mark but I am at a loss, my instinct tells me that this should be some kind of lookup, and i think I might get there in Excel but in Access I can't.

I think that somewhere on the forum the issue of the max number of nested If's is tackled but I don't know where and I also don't believe that it is the way to go.

Brian

 

[WayneRyan]

Mark,

I think that the root of the problem is that your tSalary table is not normalized. Search the forum for "normalization".

Assuming that you have access to the [Grade] and [Step], it should be a simple
matter to retrieve the value from tSalary.

BUT, while you correctly have a row for each Grade, the values for each step
are represented by a repeating field --> Step000, Step005, Step010

I'd actually rethink the design of tSalary, because it breeds exactly the kind
of problem you have now.

As a quirky workaround, you could actually use a generate the SQL to retrieve the
by supplying the "right name" for which step field to use. I wouldn't suggest
doing this though.

The real solution is to redesign the tSalary table and eliminate the repeating
fields.


Wayne

 

[folkie]

Wayne,

I probably can't spend much more time on this right now, but I was thinking normalization related to breaking the table into smaller tables so things wouldn't repeat in 1 big table.

Since there are 902 (= 22 x 41) possible values (none repeating) of the field I'm calculating, I don't see how I can get around having 902 values in the tSalary table, unless those 902 values are in more than 1 table. The only other way I can think of doing it in 1 table is having 2 fields. One field called Salary and the other called GradeStep. This would have 902 rows. I created tSalary from a spreadsheet that was setup in the same format - grades going down and steps going across. I seem to remember using the Transpose function in Excel to change the format of a spreadsheet like this, so maybe I'll try that. But as I think about it, I'm not sure how having 902 rows in tSalary will get me the values in a function like the ones I've been trying - if/then or Select. And I don't think I can just get that value in 1 field in the query. But I'll try that.

 

[Brianwarnock]

I thought this had the feel of Excel to it, in which case the info would be retrieved with Index and Match, but in Access I agree with Wayne that a redesign is required, over my 2nd glass of wine with dinner I thought "He needs 22 tables of 41 records of 2 fields then it's easy"
I don't know which way you will go but best of luck.

Brian

 

[WayneRyan]

Mark,

One table.

tblSalary
=========
SalaryID - AutoNumber <-- Primary Key
Grade - Number <-- Combination of these two gives
Step - Number <-- a unique index (no duplicates)
Salary - Currency

Then if you have an employee table (with Grade and Step)

Or

Payroll Entries with employee number (and Grade and Step)

you can easily retrieve salary:

Select Salary 
From   tblSalary 
Where  grade = Forms!frmEmployee!Grade And
       Step  = Forms!frmEmployee!Step

902 rows is nowhere near stretching the limits of a table.
Designing it properly lets you forget about all of this code
stuff.

It also makes something like "Who has the highest salary" trivial:

Select Employee.LastName, 
       Employee.FirstName,
       tblSalary.Salary 
From   Employee Inner Join tblSalary On
         Employee.SalaryID = tblSalary.SalaryID
Order By tblSalary.Salary DESC

hth,
Wayne

 

[Brianwarnock]

Are you telepathetic or whatever Wayne? I was lying awake this morning between 5:30 and 6 our time when all of a sudden I thought just one 92 row table is the correct approach. Did you send a message through the ether?

Apologies for all the rubbish I wrote on this thread, I hope I didn't waste anybodies time, I was only trying to help.

Brian

 

[WayneRyan]

Brian,

Not to worry, I could just tell that the table design was forcing Mark to
write a LOT of code when he really didn't have to.

Fortunately for me, I have absolutely ZERO ability with Excel and
therefore haven't modeled many things in a spreadsheet manner.

Have a good one,
Wayne