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

    Adjusting Query Result (2000)

    Hi,

    I've built a query that is used to populate a report. The query is a combination of data from tables and 2 formulas applied to the table data. The query and formulas are woking fine with the RARE exception when formula 1 results in zero. Although this is the correct answer, it results in a zero in formula 2 which is not the correct answer. I'm looking for a way or the best place to put an if / than statement to adjust for this. The statement would read something like:

    If me.AdjustedSalesTax.value = 0 then
    me.adjustedSalesTax.value = me.AmountAllocated *.06
    End IF

    If there is a better way to do this than I'm open to that as well.

    Thanks,
    Leesha

  2. #2
    New Lounger
    Join Date
    Jul 2001
    Location
    Scottsdale, Arizona, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    You could just use an 'IIF' statement in your query formula, which uses syntax [b]IIf (

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

    Re: Adjusting Query Result (2000)

    Hi Eric,

    Thanks for your response. Quick question, this is gonna sound really dumb, but do I put this in as a new field or as a criteria??? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Thanks,
    Leesha

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    Scottsdale, Arizona, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    I'm assuming that you already have a couple of formulas in your query design grid? This would just be another formula replacing the one which gives you the unwanted '0' result. See the attached image.
    Attached Images Attached Images

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

    Re: Adjusting Query Result (2000)

    Thanks for the quick reply. Yes you are correct that I already have two formulas in place in the query. I will try your suggestion and keep my fingers crossed.

    Thanks,
    Leesha

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

    Re: Adjusting Query Result (2000)

    Eric,

    In reading your code, I noted that you refered to tables for each field. One field, AdjustedSalesTax, actually comes from a forumla. I'm assuming I just leave out the references to a table???

    Here is the SQL I presently have:
    SELECT tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, tblPayment.DatePaid, tblDemographics.TaxExemptStatus, tblBilling.PreTaxTotal, tblBilling.SalesTax, [amountallocated]-[salestax] AS AdjustedAllocation, Year([datepaid]) AS [year]
    FROM tblPayment INNER JOIN (tblDemographics INNER JOIN (tblBilling INNER JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
    WHERE (((tblDemographics.TaxExemptStatus)=No) AND ((Year([datepaid]))=[Please enter the Sales Report year]));

    Thanks,
    Leesha

  7. #7
    New Lounger
    Join Date
    Jul 2001
    Location
    Scottsdale, Arizona, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    Yes, I believe so.

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

    Re: Adjusting Query Result (2000)

    I figured I'd try it without the table reference to see what happens and used the following:

    SalesFix: IIf([AdjustedSalesTax]=0,[tblPaymentAllocations]![AmountAllocated]*0.06,[tblAmountAllocated]![AmountAllocated]*[AdjustedSalesTax])

    I get a parameter error asking for AdjustSalesTax). I tried putting in AdjustedSalesTax as currency but it didn't work. I've only referred to forms in defining parameters so I'm sure I didn't put it in correctly, if in fact its even possible to do.

    I appreciate your help with this. It's the first time I've used "If" statements in queries so its a whole new learning experience!

    Leesha

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

    Re: Adjusting Query Result (2000)

    I tried it but got paremeter issues. I just posted on that.

    Leesha

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    Leesha...

    Am I correct in assuming that the AdjustedSalesTax figure is coming from a control on the form?... I noticed in your first post you were using the Me. reference...
    I also noticed that the SQL that you posted doesn't have an AdjustedSalesTax field, therefore you definitely would get a parameter request...

    Where is the amount (that we are testing for 0... ie AdjustedSalesTax) coming from?

    Is it possible for you to post a stripped version of the database so that we can see the tables, query and form?

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

    Re: Adjusting Query Result (2000)

    Hi Trudi,

    I've attached the DB with only the problem query, tables and the report I'm using the query with.

    To answer your questions, AdjustedSalesTax is coming directly from the query and I use it on a form. I tried the me. reference as a shot in the dark to find a way to get the result I'm looking for. RE the parameter request, how do I word it to define it with a query?? Or, is that even possible?? AdjustedSalesTax is coming from a forumula in the query. The reason I've resorted to this is because when I used the original SalesTax from the tblBilling, I would get errors if the the invoice was partially paid. In those instances each time it was paid, the full sales tax was given in the report, thus causing the user to overpay their sales tax. This is the closest I've been able to come to a workaround.

    Thanks,
    Leesha
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    Okay Leesha... I'm looking at the database and I think I follow what you're saying... Here's our problem...

    The formula:
    SalesFix: IIf([AdjustedSalesTax]=0,[tblPaymentAllocations]![AmountAllocated]*0.06,[tblPaymentAllocations]![AmountAllocated]*[AdjustedSalesTax])
    is trying to use "AdjustedSalesTax" in a condition and a calculation, when it doesn't exist... I know you're saying that it is created in the query, but it hasn't been... At least, not in the one I'm looking at...

    Is it the Sales Tax field that we should be testing and using to calculate?
    Try: AdjustedSalesTax: IIf([SalesTax]=0,[tblPaymentAllocations]![AmountAllocated]*0.06,[tblPaymentAllocations]![AmountAllocated]*[SalesTax])
    and let me know if this gives you the results you're looking for...

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Adjusting Query Result (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    I've been at this too long. My error. I've made so many changes in this I've lost track. It should be AdjustedAllocations = 0. I went back and put in the SQL I was originally using and forgot I'd changed when I started trying the "if" code:

    SELECT tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, tblPayment.DatePaid, tblDemographics.TaxExemptStatus, tblBilling.PreTaxTotal, tblBilling.SalesTax, [amountallocated]-[salestax] AS AdjustedAllocation, [AdjustedAllocation]*0.06 AS AdjustedSalesTax, Year([datepaid]) AS [year]
    FROM tblDemographics INNER JOIN (tblPayment INNER JOIN (tblBilling INNER JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblPayment.PayID = tblPaymentAllocations.PayID) ON tblDemographics.AccountID = tblBilling.AccountID
    WHERE (((tblDemographics.TaxExemptStatus)=No));

    What I need to have happen is ....................if AdjustedAllocations = 0 then AdjustedSalesTax = AmountAllocated * .06

    Thanks!
    Leesha

  14. #14
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjusting Query Result (2000)

    Okay... I'm really confused with some of these calculations BUT I'm sure you know what you're looking for... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    So... without trying to completely understand this query setup, I believe the answer is:

    AdjustedSalesTax: IIf([AdjustedAllocation]=0, [AmountAllocated]*.06,0)

    I didn't know what you wanted the value to be if the AdjustedAllocation isn't zero (your Else value) so I just put 0... You can change that part to any field name or value you want... Just change your exisiting AdjustedSalesTax formula to this one... (BTW... since there aren't any resulting records with an AdjustedAllocation of 0, the results for the AdjustedSalesTax calculation is a column of zeros... Format the field as Currency if you need to...)

    Let me know how it goes...

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

    Re: Adjusting Query Result (2000)

    Wow!! That did the trick. I needed the "else" to be the original AdjustedAllocation amount (had it not been zero) and actually got that to work on my own.

    Thanks for pointing me in the right direction!

    Leesha

Page 1 of 2 12 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
  •