calculate two fields in a query/ report

[riam71]

i have two fields that gives you the current price and previous price. now in my query i have a field that calculates the percentage of the two fields. when i run my query, i only want to show the records that prices has gone up 5% of its current price and went down 5% less than the current price. what formula should i put under criteria to show only these numbers? or maybe i need to write an event procedure in the form to do this?

desperately need help ... thank you in advance!

riam71

 

[scking]

I've built a Table1 and added the two fields with data. Then I built the query using the query builder and opened the SQL view on the query to capture the SQL code. What it really says is where the previous price is less than 95% of the CurrentPrice (Current costing 5% over previous cost) or the PreviousPrice is 105% (Current costing 5% less than previous) then select it.

SELECT Table1.CurrentPrice, Table1.PreviousPrice
FROM Table1
WHERE (((Table1.PreviousPrice)<[CurrentPrice]*0.95)) OR (((Table1.PreviousPrice)>[CurrentPrice]*1.05));

 

[LarryDeLaruelle]

I think the easiest way would be to put this in the criteria for the query in the column where you have the calculation. I would put each criteria on a separate line.

>=.05 (or whatever format you have the result)
<=.05 (ditto)

Let me know if this answers your question.

Larry De Laruelle
larry1de@yahoo.com

 

[riam71]

Thank you scking and also Larry. I will try both suggestions, hopefully this will solve my problem.

Thanks again!

riam71

 

[Quehay]

Would it work to use the Abs function? *Your wording indicates that you only want to see those whose change equals 5%, is this correct?

WHERE Abs([qryfield]) = .05

 

[raskew]

A boolean field in the query:
[current]>=([previous]*1.05) Or [previous]>=([current]*1.05) AS theRange

...with it's criteria set to True, will return only those records which equal or exceed the +/- 5% range, i.e:

SELECT tblPrices.previous, tblPrices.current, [current]>=([previous]*1.05) Or [previous]>=([current]*1.05) AS theRange
FROM tblPrices
WHERE ((([current]>=([previous]*1.05) Or [previous]>=([current]*1.05))=True));