Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Difference Question (A2K SP3)

    The code is the first Private Sub works but the code in the second Private Sub does not exclude the Pay Codes 7, 9 or 12. Why won

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Difference Question (A2K SP3)

    In the second example, you should use And instead of Or:

    strWhere ="((PayID <>7 And PayID <> 9 And PayID <> 12) ...

    You want to specify that PayID is not equal to 7 and neither equal to 9 and neither equal to 12.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    I changed the code as follows and it still does not exclude Pay IDs 7, 9, 12

    Private Sub Report_Open(Cancel As Integer)
    Dim strWhere As String
    strWhere = "((PayID <>7 and PayID <> 9 and PayId <> 12) and PState='NC' and (RentPaidDate Between ReportBeg and ReportEnd))or ((PayID2 <> 7 and PayID2 <> 9 and PayID2 <> 12) and PState = 'NC' and (RentPaidDate Between ReportBeg and ReportEnd))"
    DoCmd.OpenReport "rptNCTaxReport", acViewPreview, , strWhere

    End Sub


    Any other suggestions?

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Difference Question (A2K SP3)

    The Where-condition you now have is logically equivalent to the original one (using PayID=1 Or PayID=2 Or ...), and in a test, both yielded exactly the same result. Both conditions will NOT exclude all records for which PayID or PayID2 is 7, 9 or 12, but only records for which both PayID and PayID2 are among 7, 9 and 12. So for example a record with PayID = 3 and PayID2 = 7 will be included, as will a record with PayID = 12 and PayID2 = 11 (if, of course, PState and RendPaidDate satisfy the conditions), but a record with PayID = 9 and PayID2 = 7 will be excluded.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    I am getting different results from the 2 statements.

    PayCodeID 7 = Invoiced
    PayCodeID 9 =Past Due
    PayCodeID 12=Void

    The PayCode2 are for split pays where a customer pays partly by one method and part by another method.

    Paycode 12 would never be split. If the record is void it is PayCode= 12 and PayCode2 would be blank.

    PayCodes 7 and 9.
    You would never have PayCode =7 with a PayCode2=9 or vice versa
    If you have a Split Pay and either PayCode or PayCode2 is 7 or 9 and the other is a non excluded code, the record should be included. For example, if PayCode=1 and PayCode2 = 9, the record would be included. Likewise is PayCode = 7 and PayCode2 = 2, the record would be included.

    I think the code with the equals is working but the code is not working for the not equals.

    I will try to send a stripped down database tomorrow.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Difference Question (A2K SP3)

    It would help to see a stripped down version of the database, and also to know exactly which combinations of PayID and PayID2 you are trying to exclude.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Code Difference Question (A2K SP3)

    How about this instead:

    strwhere = "(PayID NOT IN (7,9,12) or PayID2 NOT IN (7,9,12)) AND PState='NC' AND RentPaidDate BETWEEN ReportBeg AND ReportEnd"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    Thanks for the suggestion. I get the same incorrect results. See database in reply to Hans.

    Tom

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Code Difference Question (A2K SP3)

    I'm guessing your problem is with those items in which there is a PayID but no PayID2. In this case, is PayID2 Null? Looking at your logic, if PayID is 7,9,or 12, then you look at PayID2. If PayID2 is Null, then it isn't 7,9, or 12, so it passes! I would change my suggested WHERE clause to this:

    strwhere = "(PayID NOT IN (7,9,12) or nz(PayID2,12) NOT IN (7,9,12)) AND PState='NC' AND RentPaidDate BETWEEN ReportBeg AND ReportEnd"

    If PayID2 is Null, the system will treat it like a 12.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    In the attached databse the WeeklyIncomeNoReceivables report produces the correct tax amounts.

    Also, rptNCTaxReport produces the correct tax amounts. In uses the include method in the OnOpen Event.

    RptNCTaxReport1 does not produce the correct tax amount because it fails to exclude the receivables codes 7 and 9.

    I can certainly use rptNCTaxReport but just didn't understand why the code in rptNCTaxReport1 does not work. It it is certainly shorter and Mark's code is even shorter. It also fails to exclude the code 7 and 9's.

    Tom

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    That did not correct the problem. I get the same answer with or without the of the Nz function.

    Tom

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Difference Question (A2K SP3)

    The problem is that, contrary to what you told us, PayID is not blank if PayID is (for example) 12. It is 0, a value you hadn't told us about, so I assumed that the possible values for PayID and PayID2 were 1 through 12. So the condition should be

    Not In (0,7,9,12)

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Difference Question (A2K SP3)

    Thanks. The PayCode and PayCode2 numbers go from 1-12. Upon futher checking I found that the Default value for each field was set to zero. I have an edit screen (not send with the database) that was showing the field as blank.

    I will try to check to see why.

    Thanks for your assistance.

    Tom

Posting Permissions

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