If Then Else statements (Access 2002)

Topic

New Reply

This is the situation: Everyone has an annual standard (txtAnnualStandard) there
are currently 3 different values 1144, 763, or 572 in the 13 records.
There is one person who is prorated. His annual standards once prorated is
763. His months of service is 8.
The annual standard needs to be divided by 12 giving 1144 = 95.33 or 572 =
47.67. The prorated guy is divided by 8 so 763 / 8 = 95.38.
txtProrated (Value yes/no) and txtMonthsProrated (number of months prorated)
are textboxes on the form but are not visible.

Problem is that everything comes back to 95.33. I thought I had it working correctly, but it isn’t. Here is the code

Private Sub Report_Activate()
‘ Figure monthly standard prorated vs non prorated
Dim sngMonthlyStandard As Single
txtMonthlyStandard = sngMonthlyStandard
If [txtProrated] = False Then
txtMonthlyStandard = ([txtAnnualStandard] / 12)
Else
[txtProrated] = True
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

If anyone has any ideas I would appreciate the help. Fay

Reply | Quote

Replies

What kind of variable is txtProrated? The txt prefix to the name indicates that it is a string variable, but you are conparing it to the logical value False. In addition, your statement of the problem indicates that txtProrated contains Yes/No. If txtProrated is a string, then you need to compare it to “False”, “True”, “Yes”, or “No” (with the quotes), and “False” is not the same as “No”.

You also DIM sngMonthlyStandard As Single, and then assign that variable to txtMonthlyStandard (which is never DIMed) without ever having assigned a value to sngMonthlyStandard. That should always assing txtMonthlyStandard to zero. However, that assignment really doesn’t accomplish anything since the following If statement will replace the zero with one of two other calculations.

Reply | Quote

Fay,

Put your code in the On Format Event of the detail section of the report and not in the On Activate Event.

Reply | Quote

Okay here is where I am. I placed the code in the On Format Event of the detail section. The code now looks like this.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘ Figure monthly standard prorated vs non prorated
Dim txtProrated As String
If [txtProrated] = “No” Then
txtMonthlyStandard = [txtAnnualStandard] / 12
Else
[txtProrated] = “Yes”
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

No error messages received.
Now the only resutls in the txtMonthlyStandard is the results for the Prorated person. Everyone else has no values in the txtMonthlyStandard textbox. I have even tried placing a textbox on the form with a value of 12 in it and referencing it instead of putting 12 in the formula. With no luck or results.

I appreciate the help. Thank you. Fay

Reply | Quote

What is the type of the underlying field of txtProrated ? If the field is a Yes/No field then you need to unquote the No and the Yes in your code

In a report you can’t assign a value to a bound control. Remove the line [txtProrated] = “Yes”

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘ Figure monthly standard prorated vs non prorated
Dim txtProrated As String
If [txtProrated] = No Then
txtMonthlyStandard = [txtAnnualStandard] / 12
Else
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

Reply | Quote

The table field is a Yes/No field it goes into txtProrated textbox. Which displays 0 or -1. When I run the code as listed below it now runs the formula for the Else part of the statement and places it on the proper page. But nothing I do will make the If part of the statement run. If I take the guotes off around No then I get a compiler error. Further more if I reverse the two txtMonthlyStandard formulas only the Else formula runs. I either end up with 9 correct answers or 1. (not good)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘ Figure monthly standard prorated vs non prorated
Dim txtProrated As String
If [txtProrated] = “No” Then
txtMonthlyStandard = [txtAnnualStandard] / 12
Else
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

This is making no sense to me. Thanks for your help. Fay

Reply | Quote

Have you tried the following?

If [txtProrated] = 0 Then

I hope this solves your problem.

Jack

Reply | Quote

Yes a zillion times. With quotes it figures just the Else line. If I take the quotes off I get a Run Time error 13. Thanks. Keep this up I will not need my comb because there won’t be anything thing left on my head to comb. Fay

Reply | Quote

Have you tried reversing the statement like this? Is the txtProrated field where your code can read it? Notice that I have removed the Dim statement as you should not need it. As you can see I am grasping at straws…

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If [txtProrated] = -1 Then
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
Else
txtMonthlyStandard = [txtAnnualStandard] / 12
End If

End Sub

Jack

Reply | Quote

Yahoo. It worked it must of been the Dim statement. I just copied and pasted into the code what you had written. Thank you Jack. Now I can move on to something else I don’t know what I am doing. Thanks Fay

Reply | Quote

It was my pleasure as a good wig is expensive. Continued success…

Jack

Reply | Quote