Weird Query Results using Percentage criterion

[david.semadeni]

I am working on an assignment that requires me to produce various statistics about a large set of data. The query I am struggling with required me to first generate the total $ amount of two transaction types by division and then by year. From there I had to show as a percentage for each division/year combo the total of the first field/the total of the second field. So far I have been able to accomplish all of this and have matched my results to the control totals provided by my professor. The final piece requires me to allow a user to provide a minimum percentage and show the division/years that are higher than that percentage. However, when I place any criteria into the percentage field, I get very random results. For example, I put >=.01 in the criteria field, and I get results higher and lower than 1%, but other results that are also higher and lower than 1% are not returned:

Returned using query asking for records >1%
DivID	Year	Total Invoices		Total Credit Memos	% of $CM/$Inv
1	2005	$1,262,860.96		$74,081.28	5.87%
1	2006	$1,543,216.94		$59,425.39	3.85%
2	2005	$5,825,358.94		$441,615.25	7.58%
2	2006	$5,689,277.26		$270,009.80	4.75%
3	2005	$3,738,716.26		$222,060.62	5.94%
3	2007	$5,278,996.60		$57,474.46	1.09%
4	2005	$5,397,491.57		$32,311.99	0.60%
4	2007	$6,747,668.93		$134,292.31	1.99%
5	2007	$433,278.79		$25,930.50	5.98%
9	2005	$105,270.00		$1,035.00	0.98%
9	2006	$112,030.00		$130.00	0.12%
9	2007	$106,576.00		$850.00	0.80%
11	2005	$828.89		$33,727.97	4069.05%

Missing from query asking for records with >1%

DivID	Year	Total Invoices		Total Credit Memos	% of $CM/$Inv
1	2007	$1,534,699.51		$197,248.17	12.85%
2	2007	$6,143,595.38		$255,622.67	4.16%
3	2006	$4,368,418.63		$238,157.82	5.45%
4	2006	$6,527,954.73		$778,102.97	11.92%
5	2005	$645,815.17		$69,370.00	10.74%
5	2006	$413,855.68		$68,366.00	16.52%
6	2006	$4,780,742.09		$1,148,840.29	24.03%
6	2007	$4,711,235.16		$1,201,757.92	25.51%
11	2006	$33,100.62		$30,342.50	91.67%

I do not understand what could be causing such weird results, my query and criteria match the example provided by my professor but I am not getting the results. If it were a criteria format issue (i.e. 1 was read as 100%) I should still only be getting the 4000% record.

Any help would be appreciated,

David

[June7]

Provide database for analysis. Follow instructions at bottom of my post.

[david.semadeni]

The ZIP file is still 8mb, but here is the sql for the final query:

SELECT qryReq3PreFinal.DivID, qryReq3PreFinal.Year, qryReq3PreFinal.[Total Invoices], qryReq3PreFinal.[Total Credit Memos], qryReq3PreFinal.[% of $CM/$Inv], *
FROM qryReq3PreFinal
WHERE (((qryReq3PreFinal.[Total Credit Memos])>[forms]![frmCreditMemo]![txtReq3CreditMemoMin])) OR (((qryReq3PreFinal.[% of $CM/$Inv])>[forms]![frmCreditMemo]![txtReq3cminvpct]))
ORDER BY qryReq3PreFinal.DivID, qryReq3PreFinal.Year;

We had to have a second criterion for a minimum $ amount. The second criterion is working without a problem (I tested it separately). When I set the $Amt criterion to 2,000,000 (more than the maximum for that field) and set the %criterion to 1, I get the weird % results listed in my first post.

[June7]

Can upload to fileshare site such as box.com and post link to the file.

[david.semadeni]

I put the ZIP file here http://www.box.com/s/92f9992ddac825f365b6

[June7]

I tested Val function on the percent criteria:
>Val([forms]![frmCreditMemo]![txtReq3cminvpct])

The following results are observed when I manually opened the query:

Enter .005 and records greater than .5% are displayed.
Enter .05 and records greater than 5% are displayed.
Enter .1 and records greater than 10% are displayed.
Enter 1 and records greater than 100% are displayed.
Enter 15 and records greater than 150% are displayed.

Your code behind the button click conflicts with the OR criteria of the query. It requires a value of >=1 for the CM Total and a value of >=.1 for the CM Percent. A criteria of >=1 CM Total will return every record regardless of the percent criteria and vice versa. Must allow users to NOT enter values. Null is okay but text or an empty string should be cause to reject input.

I seldom use parameter prompts in queries. I open forms or reports using the WHERE CONDITION of OpenReport or OpenForm method. I build the WHERE CONDITION statement with VBA code.

[david.semadeni]

Thank you for your assistance, I was able to resolve this issue today. In the query that my final query was pulling on I had formatted the problem fields as %, somehow having them both as % did not agree with Access. I changed the first query to fixed with 5 decimal places and was able to achieve agreeable results.