Help me write some simple codes... I am not a programmer

[PerryC]

Can someone help me accomplish the following in a MSAccess Report:

1. Check if Me.Discipline = "RN" (within the source query)
2. Then check the Me.YearOfEmployment: (again within the same source
query)
if <2 then Me.BaseRate = BaseRate,
if between 2 and <5 then Me.BaseRate = BaseRate * 1.03
if between 5 and <10 then Me.BaseRate = BaseRate * 1.03 * 1.03
....

Does the following code make sense?

If Me.Discipline = "RN" Then
If Me.YearOfEmployment < 2 Then
Me.BaseRate = Me.BaseRate * 1
ElseIf Me.YearOfEmployment >= 2 < 5 Then
Me.BaseRate = Me.BaseRate * 1.03
ElseIf Me.YearOfEmployment >= 5 < 10 Then
Me.BaseRate = Me.BaseRate * 1.0609
ElseIf Me.YearOfEmployment >= 10 < 15 Then
Me.BaseRate = Me.BaseRate * 1.09
ElseIf Me.YearOfEmployment >= 15 Then
Me.BaseRate = Me.BaseRate * 1.12
Else
Me.BaseRate = Me.BaseRate * 1
End If
End If
3. How do I bring this new defined Me.BaseRate back to the report? I
mean for the above codes, where should I insert it at? (i.e. Private
Sub Report_Open?)

Sincerely,
Perry

[Wayne Morgan]

I believe I can follow your code close enough to help. It appears you're
close to what you need. One thing I want to verify is the YearOfEmployment
is actually "years of employment", in other words, the number of years
someone has worked there, not the year they were hired.

The syntax needs a little adjusting here:

ElseIf Me.YearOfEmployment >= 2 < 5 Then<<

It should read
ElseIf Me.YearOfEmployment >= 2 And Me.YearOfEmployment < 5 Then

Do this for the rest of the statements also. At the end, you have a final
Else which gives a BaseRate of BaseRate*1. If this is going to be the
default, it is ok, but not necessary, to specify this same rate for <2. If
you don't specify it, when <2 didn't meet any of the other criteria it would
fall through to this default and the answer would be the same.

I believe you may find a different command easier to follow than all of the
If statements. Using this other command, your code would look like:

If Me.Discipline = "RN" Then
Select Case Me.YearOfEmployment
Case Is >=15
Me.BaseRate = Me.BaseRate * 1.12
Case 10 To 15
Me.BaseRate = Me.BaseRate * 1.09
Case 5 To 10
Me.BaseRate = Me.BaseRate * 1.0609
Case 2 To 5
Me.BaseRate = Me.BaseRate * 1.03
Case Else
'Since the result will be *1, we don't need to do anything here
End Select
End If

This is reversed from what you have because the Select Case statement will
execute the first True Case it finds. "Case 10 To 15" will be true, even if
the value is 15 (it won't be limited to less than 15); however, since "Case
Is >=15" comes first, the "Case 10 To 15" will never be seen.

Where to put this? It appears that you are referring to the value of a
textbox called BaseRate on the report. Place the code in the Format event
for that section of the report. This could also be done without the VBA
code, but using a calculated textbox. It will be harder to read that way
(for you, not on the report as seen by the user) but will probably be
faster. For this to work properly, the field name and the textbox name need
to be different. So, if the field name is BaseRate, change the textbox name
to txtBaseRate (this would apply for Discipline also) and place the
following in the Control Source of the textbox.

=IIf([Discipline]="RN", IIf([BaseRate]>=15, [BaseRate]*1.12,
IIf([BaseRate]>=10, [BaseRate]*1.09, IIf([BaseRate]>=5, [BaseRate]*1.0609,
IIf([BaseRate]>=2, [BaseRate]*1.03, [BaseRate])))))

This is also relying on the order of the listed items. If >=15 we get
[BaseRate]*1.12; however, if >=15 is false we then go to the next IIf
statement and see if >=10 is true. We don't need to check for 15 again,
because if the value was >=15 we wouldn't have gotten this far. If all else
fails, then just return [BaseRate]. Note that we don't need a False part for
IIf([Discipline] = "RN".... because you haven't listed what you want to do
in that case.

--
Wayne Morgan
Microsoft Access MVP
"PerryC" <pe******@yahoo.com> wrote in message
news:ea**************************@posting.google.c om... Can someone help me accomplish the following in a MSAccess Report:

1. Check if Me.Discipline = "RN" (within the source query)
2. Then check the Me.YearOfEmployment: (again within the same source
query)
if <2 then Me.BaseRate = BaseRate,
if between 2 and <5 then Me.BaseRate = BaseRate * 1.03
if between 5 and <10 then Me.BaseRate = BaseRate * 1.03 * 1.03
....

Does the following code make sense?

If Me.Discipline = "RN" Then
If Me.YearOfEmployment < 2 Then
Me.BaseRate = Me.BaseRate * 1
ElseIf Me.YearOfEmployment >= 2 < 5 Then
Me.BaseRate = Me.BaseRate * 1.03
ElseIf Me.YearOfEmployment >= 5 < 10 Then
Me.BaseRate = Me.BaseRate * 1.0609
ElseIf Me.YearOfEmployment >= 10 < 15 Then
Me.BaseRate = Me.BaseRate * 1.09
ElseIf Me.YearOfEmployment >= 15 Then
Me.BaseRate = Me.BaseRate * 1.12
Else
Me.BaseRate = Me.BaseRate * 1
End If
End If
3. How do I bring this new defined Me.BaseRate back to the report? I
mean for the above codes, where should I insert it at? (i.e. Private
Sub Report_Open?)

Sincerely,
Perry