Adjusting Query Result (2000)

Topic

New Reply

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

Reply | Quote

Replies

You could just use an ‘IIF’ statement in your query formula, which uses syntax IIf (

Reply | Quote

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???
Thanks,
Leesha

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Yes, I believe so.

Reply | Quote

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

Leesha

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

Okay… I’m really confused with some of these calculations BUT I’m sure you know what you’re looking for…
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…

Reply | Quote

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

Reply | Quote

Trudi, one more question. In instances where there would be more than one condition to look at as in If / elseif / elseif ……………..is that possible in a query and if so, do I just separate them by a comma?

Thanks,
Leesha

Reply | Quote

Sorry it took me so long to respond… I got dragged away from my desk…

Yes, it’s possible… but not by inserting a comma….

Like Eric said… The IIf (Immediate If) function’s syntax is IIf (

Reply | Quote

>>Yes, it’s possible…

Cool!

>>Like Eric said… The IIf (Immediate If) function’s syntax is IIf (

Reply | Quote

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

Which in code would look like:

If AdjustedAllocation = 0 Then
AdjustedSalesTax = AmountAllocated * .06
Else
If TaxExemptStatus = True Then
AdjustedSalesTax = 0
Else ‘neither of the IF conditions were true…
AdjustedSalesTax = AdjustedAllocation
EndIf
EndIf

Does that make sense?

Don’t worry about not knowing all of this stuff right now… Just be open to keep reading and trying things… …Soon, you’ll be giving answers too!

Reply | Quote

Hi!

It really helped having the visual of the query statement written out in code as I could understand that better. Thank you for taking the time to do it. I tried the code and it didn’t quite come out right, but when I substitued SalesTax =0 for TaxExemptStatus the results were exactly what I was looking for.

I’m simply thrilled. Thanks so much!

Leesha

Reply | Quote

Oops, I got excited too soon. The code is running correctly now for those who are tax exempt but the first portion you helped me with is giving the wrong totals. I’m going to play around with it some more but am not sure what went wrong.

Leesha

Reply | Quote

OK, this is what I did and it works. There is probably a better way, but this is what I came up with. I added another column in the query next to AdjustedSalesTax and this is how it reads:

TaxExemptAdjustedSalesTax: IIf([SalesTax]=0,0,[AdjustedSalesTax])

It ran correctly.

Well Trudi, you’ve given me a whole new area to play with!
Thanks,
Leesha

Reply | Quote