User defined criteria on Percent field

[owensbri]

I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to help me track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.

One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:

SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc
FROM tbl_OSRImport
WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));

This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:


In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%

Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032

I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.

I hope I have explained this well enough. Any ideas?

- Brian

 

[Brianwarnock]

What happens if you Round your calculation

Brian

 

[owensbri]

I considered that rounding might help, but I am not sure the best way to do it. I looked for a paremeter to set the character lenght, but could not find one on a calculated field.

- Brian

 

[Brianwarnock]

Sorry I don't understand you reply as rounding would only be done on calculated fields

Round(a/b,4)

Brian

 

[owensbri]

I meant that I did not know how to include "rounding" within the expression I am already using. I had tried to look in the field properties to see if there was an option to cap the length similar to a number a field, or decimal length.

By your reply, am I to assume that the syntax of the "rounding" is something like this:

Round([Field1]/[Field2], 4)
Where "4" would mean it will round to 4 characters in length? If so, how does that integrate into the expressions I am already using?

-Brian

 

[Brianwarnock]

The 4 is the number of decimal places that the answer is rounded to.

It is explained in help or via google

http://allenbrowne.com/round.html

Brian