Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Then Else statements (Access 2002)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    Fay,

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

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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
    Francois

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    Have you tried the following?

    If [txtProrated] = 0 Then

    I hope this solves your problem.

    Jack

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Fay

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

    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

  11. #11
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then Else statements (Access 2002)

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

    Jack

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •