Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Defining a query parameter (2000)

    Hi again,

    I'm trying to filter a field in a query that is actually a formula based on other fields in the query. There are two fields with forumulas. The one I'm trying to filter to only show balances > 0 reads as follows:

    Balance: [total billed]-[AmtAlloc]

    [AmtAmt] is arrived at via the following formula:

    AmtAlloc: nz(DSum("AmountAllocated","tblPaymentAllocations", "PayID=" & tblTimesheet.Autonumber),0)

    The query runs fine without issue till I try to filter the Balance field to only show records >0. Then I get a parameter question asking me to define AmtAlloc. I've tried putting in the the paramenter of AmtAlloc as a long integer or currency but it doesn't work. I've simply put in AmtAlloc without brackets or anything as I wasn't sure how to reference as with forms.

    Thanks,
    Alicia

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Defining a query parameter (2000)

    You may to define Balance as:

    Balance: [total billed] - nz(DSum("AmountAllocated","tblPaymentAllocations", "PayID=" & tblTimesheet.Autonumber),0)

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

    Re: Defining a query parameter (2000)

    Hey Stranger!!!

    I'll give it a shot a little later as I'm hitting the road and will let you know how it goes.

    Leesha

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

    Re: Defining a query parameter (2000)

    Hi Pat,

    That worked just the way I wanted one I put in the >0 filter and there were no parameter errors. So, as you know I "need" to understand why..............why did that forumla work without parameter issues??

    Question 2 - since its formula/query related I guess I can keep it in the same thread.............................In instances where there have been no Payments made, those cells come up blank in the query even though I've set the default to zero in the table. I'm assuming its because there is no related record in either tblPayments or tblPaymentAllocations for the invoice in tblTimesheet, therefore the default zero isn't applied. I would like there to be zero dollars in those cells but wasn't sure how to get do it.

    Thanks!

    Leesha

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Defining a query parameter (2000)

    I have no idea why it works, just that it does.

    On the 2nd question use the NZ function.

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

    Re: Defining a query parameter (2000)

    By putting criteria on Balance, you're introducing too many levels of complexity into one query:
    - A calculated field AmtAlloc
    - A calculated field Balance that uses the value of AmtAlloc
    - A filter on Balance

    Pat's solution takes out one level by putting the formula for AmtAlloc into the definition of Balance.

    An alternative would be:
    - Omit the criteria from the query, and save it.
    - Create a new query based on the first one.
    - Put the criteria into this new query.

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

    Re: Defining a query parameter (2000)

    Thanks for the explanation!! If it makes sense to me I'm more apt to remember it and how it was done to duplicate it in the future. I can see I'm going to have fun using queries in queries. The possibilites are endless!!

    Speaking of help, that "esc." hint re the referential integrity saved me a lot of frustrating phone calls today.

    Thanks,
    Leesha

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

    Re: Defining a query parameter (2000)

    Uhm......................hwo would I write this??

    Leesha

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Defining a query parameter (2000)

    The NZ function works like:
    Nz(Fieldtotestfornull,0)
    This tests the field Fieldtotestfornullfor Null and if so substitutes zero.

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

    Re: Defining a query parameter (2000)

    Hi Pat,

    Thanks for giving me the info on how to set this up as well as the explanation of the hows and whys!

    I'm blind from staring at the computer too late last night and am going to take a break from it for the day I think. I found that one of the formula's I've been using in two different databases is in fact NOT giving me what I want. The goal was for it to give me the balance to an invoice after each allocation applied and its not. Instead it seems to be giving me the total amount applied. I've played with the formulas so many times now I don't even recall what the original one was. What I need is the following scenerio:

    Invoice # Total billed - payment allocation = balance
    so that the next payment allocation would look something like

    Invoice# balance - payment allocation = balance ........................and so on till there is a zero balance.

    I'm wondering whether I need to do it in two stages???

    What I found is that when I use the forumula you gave me up above it gives me the overall balance after all the allocations have been deducated from that invoice. In otherwords, if there have been three allocations made to an invoice, that balance for each allocation is the same - which is the balance after ALL allocations have been deducted vs a different balance each time.

    Any thoughts?

    Thanks,
    Alicia

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Defining a query parameter (2000)

    Hi Leesh

    From one of your databases in the past the following gives a list of Payment Allocations against and shows the balance correctly (I think, I have tested it and it seems to work).

    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], [Salutation] & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName/BusinessName] & " " & [Suffix] AS Expr1, tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID, tblBilling.InvoiceTotal, tblPaymentAllocations.AmountAllocated, [InvoiceTotal]-[AmountAllocated]-Val(nz(DSum("AmountAllocated","tblPaymentAllocatio ns","InvoiceNoReference=" & tblPaymentAllocations.InvoiceNoReference & " AND PayAllocID <" & tblPaymentAllocations.PayAllocID),0)) AS Balance, tblPayment.Amount, tblPayment.DatePaid, tblPayment.PayID
    FROM tblDemographics INNER JOIN (tblBilling INNER JOIN (tblPayment INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID) ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID;

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

    Re: Defining a query parameter (2000)

    Hi Pat,

    THANK YOU so much. That does what I want it to and I NEVER would've figured that one out on my own. The database I'm working on is a different on, but I was able to substitute the correct info and make it work.

    I appreciate it!

    Leesha

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

    Re: Defining a query parameter (2000)

    OK, now here's my problem again. That formula works beautifully but it won't show records that have no payments applied. I tried creating two queries as Han's suggested and I'm getting and "#error" in the balance field if no payments have been applied, when what I want is a zero. Any ideas?

    Thanks,
    Leesha

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

    Re: Defining a query parameter (2000)

    Hi,

    I've enclosed a stripped down database that has been saved to Access 97 to make it small enough. It contains two queries. qryProblemForumla is the one that I need help with. It's getting error messages and in records where no payment has been made there is an error in the balance field. I'm assuming its because the references the formula are looking for aren't there. However if I try to do the entire operation in one qry, no matter how I do the joins I do not get all of the [Autonumber] from tblTimesheet, which I need.

    The formula in [balance] works fine as long as I don't try to then go after records that didn't have payments applied.

    Any help would be appreciated.

    Thanks,
    Alicia
    Attached Files Attached Files

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Defining a query parameter (2000)

    I would tend to build a query to show allocations against timesheets (which you already have) and a query where there is no allocations against timesheets, then combine both queries in a UNION query.

Page 1 of 4 123 ... LastLast

Posting Permissions

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