Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2006
    Posts
    10

    Question Unanswered: Halfway there, need some help! (FITW)

    Hi, I just need some help, if anyone has any ideas. I'm fairly new at using Access, and I have an older version (2000). I'm trying to design a program to track my husband's union dues, expenses, etc. I've got the paycheck calculator almost done, but I'm having a hard time getting the VB code to run. Here is what I have so far:

    Private Sub Forms(PaycheckCalculator)
    AfterUpdate
    Me!FederalWH.Visible = True

    IIf [Federal Filing Status] = "Single", ([Gross Amount] - ([#of Deductions] * 63.46))
    IIf ([Gross Amount] - ([#of Deductions] * 63.46) < 51), [Federal W/H] = 0
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">51 OR <192", [Federal W/H] = [Gross Amount] * 0.1
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">192 OR <620", [Federal W/H] = [Gross Amount] * 0.15
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">620 OR <1409", [Federal W/H] = [Gross Amount] * 0.25
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">1409 OR <3013", [Federal W/H] = [Gross Amount] * 0.28
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">3013 OR <6508", [Federal W/H] = [Gross Amount] * 0.33
    IIf ([Gross Amount] - ([#of Deductions] * 63.46) > 6508), [Federal W/H] = [Gross Amount] * 0.35


    IIf [Federal Filing Status] = "Married", ([Gross Amount] - ([#of Deductions] * 63.46))
    IIf ([Gross Amount] - ([#of Deductions] * 63.46) < 154), [Federal W/H] = 0
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">154 OR <440", [Federal W/H] = [Gross Amount] * 0.1
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">440 OR <1308", [Federal W/H] = [Gross Amount] * 0.15
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">1308 OR <2440", [Federal W/H] = [Gross Amount] * 0.25
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">2440 OR <3759", [Federal W/H] = [Gross Amount] * 0.28
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">3759 OR <6607", [Federal W/H] = [Gross Amount] * 0.33
    IIf ([Gross Amount] - ([#of Deductions] * 63.46) > 6607), [Federal W/H] = [Gross Amount] * 0.35

    End Sub

    The equations work, but they are too long to put in the properties box, so I tried to put the code in VB. Can anyone tell me what I'm doing wrong? Thanks in advance.

    azchick

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try this.......

    Code:
    Dim Total As Currency
    Dim GrossAmount As Currency
    Dim NumberOfDeductions As Integer
     
    GrossAmount = Nz(Me.[Gross Amount], 0)
    NumberOfDeductions = Nz(Me.[#of Deductions], 0)
     
    If Me.[Federal Filing Status] = "Single" then
       Select Case GrossAmount - (NumberOfDeductions * 63.46)
          Case < 51
             Total = 0
          Case >51 OR <192
             Total = GrossAmount * 0.1
          Case >192 OR <620
             Total = GrossAmount * 0.15
          Case >620 OR <1409
             Total = GrossAmount * 0.25
          Case >1409 OR <3013
             Total = GrossAmount * 0.28
          Case >3013 OR <6508
             Total = GrossAmount * 0.33
          Case > 6508
             Total = GrossAmount * 0.35
       End Select
    ElseIf Me.[Federal Filing Status] = "Married" then
       Select Case GrossAmount - (NumberOfDeductions * 63.46)
          Case < 154
             Total = 0
          Case >154 OR <440
             Total = GrossAmount * 0.1
          Case >440 OR <1308
             Total = GrossAmount * 0.15
          Case >1308 OR <2440
             Total = GrossAmount * 0.25
          Case >2440 OR <3759
             Total = GrossAmount * 0.28
          Case >3759 OR <6607
             Total = GrossAmount * 0.33
          Case > 6607
             Total = GrossAmount * 0.35
       End Select
    End If
    Me.[Federal W/H] = Total
    Place this code into the OnClick code event for a Command Button (or whereever).

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    Quote Originally Posted by azchick
    IIf ([Gross Amount] - ([#of Deductions] * 63.46) < 51), [Federal W/H] = 0
    IIf ([Gross Amount] - ([#of Deductions] * 63.46)), ">51 OR <192", [Federal W/H] = [Gross Amount] * 0.1
    Is your logic correct ? For instance do you need to consider the case where the calculation is exactly equal to 51 ?

    Also, any real number can meet the criteria >51 OR <192. Do you really mean >51 AND <192 ?

    If so make the relevant changes in CyberLynx's code.

    Chris

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Good point Chris,

    >50 OR <192

    OR

    >=51 OR <192

    Should do the trick. You will have to consider this with all your values by the REAL look of it.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Feb 2006
    Posts
    10
    Thanks guys, I will be working on this tommorrow from the office, I will try to post when I get home to let you know if it worked.

    AZchick

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by CyberLynx
    Good point Chris,

    >50 OR <192

    OR

    >=51 OR <192

    Should do the trick. You will have to consider this with all your values by the REAL look of it.

    .
    By REAL I meant any number from minus infinity to plus infinity. The logic should be:
    >=51 AND <192
    i.e. replace OR with AND otherwise you can pick any number and it will be true.

    Chris

  7. #7
    Join Date
    Feb 2006
    Posts
    10
    Hi guys, I tried to input the code, and VB accepted all but a few things that I had to change around. There is now only one line that isn't running right, and I'm not sure why. Here's the error I'm getting:

    Private Sub Federal_W_H_Click()
    Dim Total As Currency
    Dim GrossAmount As Currency
    Dim NumberOfDeductions As Integer

    GrossAmount = Nz(Me.GrossAmount, 0)
    NumberOfDeductions = Nz(Me.NumberOfDeductions, 0)

    If Me.[Federal Filing Status] = "Single" Then
    Select Case GrossAmount - (NumberOfDeductions * 63.46)
    Case Is < 51
    Total = 0
    Case ">51 AND <192"
    Total = GrossAmount * 0.1
    Case ">192 AND <620"
    Total = GrossAmount * 0.15
    Case ">620 AND <1409"
    Total = GrossAmount * 0.25
    Case ">1409 AND <3013"
    Total = GrossAmount * 0.28
    Case ">3013 AND <6508"
    Total = GrossAmount * 0.33
    Case Is > 6508
    Total = GrossAmount * 0.35
    End Select
    ElseIf Me.[Federal Filing Status] = "Married" Then
    Select Case GrossAmount - (NumberOfDeductions * 63.46)
    Case Is < 154
    Total = 0
    Case ">154 AND <440"
    Total = GrossAmount * 0.1
    Case ">440 AND <1308"
    Total = GrossAmount * 0.15
    Case ">1308 AND <2440"
    Total = GrossAmount * 0.25
    Case ">2440 AND <3759"
    Total = GrossAmount * 0.28
    Case ">3759 AND <6607"
    Total = GrossAmount * 0.33
    Case Is > 6607
    Total = GrossAmount * 0.35
    End Select
    End If

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the error is?, its a little hard to tell without the facts
    I note you still haven't corrected the error identified earlier

    Case ">51 AND <192"
    Total = GrossAmount * 0.1
    Case ">192 AND <620"

    means that a value of 192 will not have any rate applied to it.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    would you not be better pulling these values in from a table, that way round it can be adjusted as required by changing the data not the application.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2006
    Posts
    10
    The area I highlighted in an earlier post is where it keeps stopping. I am getting a run time error, and that is the section that is highlighted when I hit Debug. Here are the changes I have made so far:

    Private Sub Federal_W_H_Click()
    Dim Total As Currency
    Dim GrossAmount As Currency
    Dim NumberOfDeductions As Integer

    GrossAmount = Nz(Me.GrossAmount, 0)
    NumberOfDeductions = Nz(Me.NumberOfDeductions, 0)

    If Me.[Federal Filing Status] = "Single" Then
    Select Case GrossAmount - (NumberOfDeductions * 63.46)
    Case Is < 51
    Total = 0
    Case ">=51 AND <192"
    Total = GrossAmount * 0.1
    Case ">=192 AND <620"
    Total = GrossAmount * 0.15
    Case ">=620 AND <1409"
    Total = GrossAmount * 0.25
    Case ">=1409 AND <3013"
    Total = GrossAmount * 0.28
    Case ">=3013 AND <6508"
    Total = GrossAmount * 0.33
    Case Is >= 6508
    Total = GrossAmount * 0.35
    End Select
    ElseIf Me.[Federal Filing Status] = "Married" Then
    Select Case GrossAmount - (NumberOfDeductions * 63.46)
    Case Is < 154
    Total = 0
    Case ">=154 AND <440"
    Total = GrossAmount * 0.1
    Case ">=440 AND <1308"
    Total = GrossAmount * 0.15
    Case ">=1308 AND <2440"
    Total = GrossAmount * 0.25
    Case ">=2440 AND <3759"
    Total = GrossAmount * 0.28
    Case ">=3759 AND <6607"
    Total = GrossAmount * 0.33
    Case Is >= 6607
    Total = GrossAmount * 0.35
    End Select
    End If
    Me.[Federal W/H] = Total
    End Sub



    I have highlighted the current error in red. This is where the program leads me when I hit 'Debug'. If anyone has any suggestions, I would be eternally grateful. Thanks.

    AZchick

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    Sorry for the late reply.....

    Whoops

    Code:
    Dim Total As Currency Dim GrossAmount As Currency Dim NumberOfDeductions As Integer GrossAmount = Nz(Me.[Gross Amount], 0) NumberOfDeductions = Nz(Me.[#of Deductions], 0) If Me.[Federal Filing Status] = "Single" then Select Case GrossAmount - (NumberOfDeductions * 63.46) Case Is < 51 Total = 0 Case 51 To 191 Total = GrossAmount * 0.1 Case 192 To 619 Total = GrossAmount * 0.15 Case 620 To 1408 Total = GrossAmount * 0.25 Case 1409 To 3012 Total = GrossAmount * 0.28 Case 3013 To 6508 Total = GrossAmount * 0.33 Case Is > 6508 Total = GrossAmount * 0.35 End Select ElseIf Me.[Federal Filing Status] = "Married" then Select Case GrossAmount - (NumberOfDeductions * 63.46) Case Is < 154 Total = 0 Case 155 To 440 Total = GrossAmount * 0.1 Case 441 To 1308 Total = GrossAmount * 0.15 Case 1309 To 2440 Total = GrossAmount * 0.25 Case 2441 To 3759 Total = GrossAmount * 0.28 Case 3760 To 6607 Total = GrossAmount * 0.33 Case Is > 6607 Total = GrossAmount * 0.35 End Select End If Me.[Federal W/H] = Total
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  12. #12
    Join Date
    Feb 2006
    Posts
    10
    Can anyone point me to a cheat sheet or library that explains what each of the lines does? I am wondering if maybe I can't figure out the error if I knew what the code lines meant.

    Thanks

    AZchick

Posting Permissions

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