updating a field in a subform with a calculation from 2 other fields

Access 2010   vba

I'm updating a field in a subform with a calculation from 2 other fields.

Is there an easier way to write this  code ?



' =====================================
Dim g As String
Dim j As String

g = Me!RENEWAL_ESCALATED_PRICE_INCREASE_FRM
j = Me!ESTIMATED_REVENUE_IMPACT_FRM

' ============================================
'  < 4%
If g < 4 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' < 4%
If g < 4 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If
' < 4%
If g < 4 And j > 1000 Then
Me!ACTION_NEEDED_FRM = "FVP Approval"
End If


' =========================================
'  >4  and < 8%
If g > 4 And g < 8 And j < 250 Then
 Me!ACTION_NEEDED_FRM = "Update Quote"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 1000 Then
 Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

' ===========================================
'  > 8%
If g > 8 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
A Select Case would be clearer and less redundant.  It also looks like j should be the primary test.

When j < 250, g is irrelevant

There is an error also since if the value of j is exactly 250 or g = 8, they doesn't fit any criteria.

Please review this carefully since I modified it to handle 250:
Select Case j 
    Case < 250 
        Me.ACTION_NEEDED_FRM = "Update Quote"
    Case >= 250 and < 1000
        If g > 8 Then
            Me.ACTION_NEEDED_FRM = "Update Quote"
        Else
            Me.ACTION_NEEDED_FRM = "RSVP/Director Approval"
        End If
    Case Else
        Select Case g
            Case > 8 
                Me.ACTION_NEEDED_FRM = "Update Quote" 
            Case < 4   
                Me.ACTION_NEEDED_FRM = "FVP Approval"   
            Case Else
                Me.ACTION_NEEDED_FRM = "RSVP/Director Approval"
        End Select 
End Select

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks pat.as always
PatHartmanCommented:
You're welcome.  Please test carefully since your original code was not processing certain values at all.
FordraidersAuthor Commented:
pat , sorry to get back on this:

getting an error on this line

Case >= 250 and < 1000  from above

Thanks
fordraiders
PatHartmanCommented:
You might want to share the error message/number.  Is it a compile error or a runtime error?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.