Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query Help Needed (2002)

    Hi,

    I have a database that is used for billing. It

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

    Re: Query Help Needed (2002)

    I won't pretend to understand the queries, but does the attached do what you want? I only modified qryPaymentSum1A in two ways:

    - I reversed the direction of the outer join.
    - I added [b]Or Is Null]/b] to the criteria for the filter field.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Help Needed (2002)

    Hi Hans,

    Thanks so much for not asking me to explain the queries! I really appreciate that one LOL. When I went to open the file, after downloading, I keep getting a message that it can't be opened due to security reasons due to being located outside my intranet. I've never had that happen before. I've saved it to the HD etc. just as I always do.

    Leesha

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

    Re: Query Help Needed (2002)

    Do you get this warning with the zip file or with the database?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Help Needed (2002)

    Hi Leesha

    I can open it from my home network...I imaging it maybe your firewall settings <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Jerry

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

    Re: Query Help Needed (2002)

    Added later: see <post#=582,484>post 582,484</post#>.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Help Needed (2002)

    First I get the unknown publisher error which I OK that it is OK to open then I get the attached message.

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Help Needed (2002)

    HI,

    Turned off the firewall settings but still have the same issue.

    Leesha

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

    Re: Query Help Needed (2002)

    Have you tried the method to unblock the file from the post I referred to?

    Note: you should first download the zip file to your hard disk (instead of opening it directly from the link in my reply), then open the downloaded zip file and extract the database.

    If you still have problems, here is the modified SQL of the qryPaymentSum1A query:

    SELECT tblPayments.[Walmart number], tblPayments.[Invoice Payment Amount], tblPayments.PrepayAmount, 100*Year([payment date])+Month([payment date]) AS filter, tblInvoice.GreaseTrapFeeAdjusted, tblInvoice.UtilityFeeAdjusted, tblInvoice.[TTTS Management Fee], nz([tier1fee])+nz([tier2fee])+nz([tier3fee])+nz([tier4fee])+nz([tier5fee]) AS [Total Rent Paid], tblInvoice.[TTTS Management Fee] AS [Total TTTSM Paid], tblInvoice.GreaseTrapFeeAdjusted AS [Total Grease Trap Fee Paid], tblInvoice.UtilityFeeAdjusted AS [Total Utility Fee Paid], IIf([greasetrapfeeadjusted]>0 Or [utilityfeeadjusted]>0,nz([tier1fee])+nz([tier2fee])+nz([tier3fee])+nz([tier4fee])+nz([tier5fee])+nz([greasetrapfeeadjusted])+nz([utilityfeeadjusted]),0) AS [Rent/Utility/GreaseTrapBilled]
    FROM tblPayments RIGHT JOIN tblInvoice ON tblPayments.[Invoice#] = tblInvoice.InvoiceNumber
    GROUP BY tblPayments.[Walmart number], tblPayments.[Invoice Payment Amount], tblPayments.PrepayAmount, 100*Year([payment date])+Month([payment date]), tblInvoice.GreaseTrapFeeAdjusted, tblInvoice.UtilityFeeAdjusted, tblInvoice.[TTTS Management Fee], nz([tier1fee])+nz([tier2fee])+nz([tier3fee])+nz([tier4fee])+nz([tier5fee]), tblInvoice.[TTTS Management Fee], tblInvoice.GreaseTrapFeeAdjusted, tblInvoice.UtilityFeeAdjusted, IIf([greasetrapfeeadjusted]>0 Or [utilityfeeadjusted]>0,nz([tier1fee])+nz([tier2fee])+nz([tier3fee])+nz([tier4fee])+nz([tier5fee])+nz([greasetrapfeeadjusted])+nz([utilityfeeadjusted]),0)
    HAVING (((100*Year([payment date])+Month([payment date]))<=[forms]![frmPrintinvoices]![txtfilter] Or (100*Year([payment date])+Month([payment date])) Is Null));

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Help Needed (2002)

    Hi Hans,

    I have been copying the database to my HD. Never had this issue before. Anyway, thatnks for including the SQL. I copied and it seems to be doing what I need it to. Now the test is being sure if doesn't adversely effect the other bills where payment has been made. It gets me on the right track!!

    Thanks,
    Leesha

Posting Permissions

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