To use the IIf...Then function or not to use, this is the question! (1 Viewer)

[BLT1976]

Okay! I have tried all day to come up with the answer to my dilemma and simply have hit my capacity. I am sure this will be a cake walk for someone out there.

I have a table with status field indicating whether the person covered has Single (S), Family (F), etc. coverage. There is another table with the rates. The individual's record connects to the rates through a Location Code and a Medical Rate Code. However, there are still four rates to choose from based on their status field (Single, Family, etc.).

What I need to do is for the one field in the Benefits table to select which field in the Medical Rates table in order to select the distinct rate for that employee. Here is my sad attempt:

If ([Benefits]![EE Covg]="S") Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE]
ElseIf ([Benefits]![EE Covg]="F") Then
[Benefits]![Med Mon Rate]= [Medical Rates]![Fam]
ElseIf ([Benefits]![EE Covg]="1") Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE+1]
ElseIf ([Benefits]![EE Covg]="2") Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE/Kids]
ElseIf ([Benefits]![EE Covg]="W") Then
[Benefits]![Med Mon Rate]="0"
End If

I get "Syntax Error" everytime I try this one.

Is this the right format to use? Should I use Select Case? Also, do I simply do an Update Query and add the information to a separate field in the Benefits table or can I run a query that will simply contain the selected rate within it?

Thanks for anyone who can help on this.

Brandon

 

[Cosmos75]

Try Select Case

 

[JonH]

I think if you move the closing parentheses you would lose the syntax error -

If ([Benefits]![EE Covg])="S" Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE]
ElseIf ([Benefits]![EE Covg])="F" Then
[Benefits]![Med Mon Rate]= [Medical Rates]![Fam]
ElseIf ([Benefits]![EE Covg])="1" Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE+1]
ElseIf ([Benefits]![EE Covg])="2" Then
[Benefits]![Med Mon Rate]= [Medical Rates]![EE/Kids]
ElseIf ([Benefits]![EE Covg])="W" Then
[Benefits]![Med Mon Rate]="0"
End If

Jon.

 

[BLT1976]

Thanks...but?

Thanks. I figured that was what I was supposed to be using. Here is what I came up with though:

Select Case [Benefits]![EE Covg]
Case "S"
[Benefits]![Med Mon Rate]= [Medical Rates]![EE]
Case "F"
[Benefits]![Med Mon Rate]= [Medical Rates]![Fam]
Case "1"
[Benefits]![Med Mon Rate]= [Medical Rates]![EE+1]
Case "2"
[Benefits]![Med Mon Rate]= [Medical Rates]![EE/Kids]
Case "W"
[Benefits]![Med Mon Rate]="0"
End Select

Where can I fix this? ALSO, do I do an Update Query, just run a query, or what? It seems with the Select Case function, it wants to place a value in a blank table field. Can you just have it show in a query?

Thanks for any help!

Brandon

 

[JonH]

If you put the intended value into a variable derived from a table or hardcoded such as

Dim strBenefits as String

Select Case [Benefits]![EE Covg]
Case "S"
strBenefits = "EE"
Case "F"
strBenefits = "Fam
"Case "1"
strBenefits = "EE +1
Case "2"
strBenefits = EE/Kids
Case "W"
strBenefits = "0"
End Select

You woud then update the record(set) making the relvant field from the Recordset equal to strBenefits. Where rst is the Recordset containing the record that you wish to update.

with rst
.Edit
!Relevant_Field = strBenefits
.Update
End With

Jon.

 

[The_Doc_Man]

If you wanted to do something similar to this in a query, don't use SELECT CASE. What you want is the query equivalent, which is called "SWITCH". Look up the help on the SWITCH function.

Using it in a query would be tedious but not impossible. The first couple of elements would be...

Table: Benefits
Field: Med Mon Rate
Update to: =Switch( [Benefits].[EE Covg]="S", [Medical Rates].[EE]., [Benefits].[EE Covg]="F", [Medical Rates].[Fam], ..... )


However, this appears to be a highly non-normalized case. You have made work for yourself.

Your Medical Rates table should contain
-- code-letter
-- rate-name
-- rate
-- other info specific to current code-letter only

Then you could do a JOIN of the medical rates table to the Benefits table using the code letter from the rates table to join to the coverage code from the benefits table. After that, the update would be just copying data from one field to another within the joined record. For Access, it would be a piece of cake.

 

[BLT1976]

Thanks Everyone. I went a simpler direction and created a key to connect the people with their plans as we had done the first round. We were trying to link plans with people by three seperate fields and it was getting to be too much for me to learn how to program.

Thanks! I will use this in the future. And Doc_Man, I thought about using the Switch, but I was very uncertain. Thanks for your clear explanation.

Brandon

 

[Pat Hartman]

You wouldn't need any code at all if you properly normalized your table. You need one more key field so that you can eliminate the repeating group of rates. In addition to Location Code and Medical Rate Code, the rate table key should include the status field as well. That way, each row in the rate table would contain only a single rate. You would obtain the proper rate by joining on all three fields to the rate table.