Parameter/Criteria settings for percentage expression field

A

Ammo

Hi,

I have created the following query:

SELECT school_details_tbl.CostCentre, school_details_tbl.SchoolName,
Sum(DateDiff("n",[VisitStartTime],[VisitFinishTime])/60) AS [Total Visit
Hours Completed], school_details_tbl.VisitHoursPurchased, [Total Visit Hours
Completed]/[VisitHoursPurchased] AS [% Visits Completed]
FROM school_details_tbl INNER JOIN visit_details_tbl ON
school_details_tbl.CostCentre = visit_details_tbl.CostCentre
GROUP BY school_details_tbl.CostCentre, school_details_tbl.SchoolName,
school_details_tbl.VisitHoursPurchased;

I am attempting to insert Criteria under the '% Visits Completed' expression
field I have created so that it only displays records that have a '% Visits
Completed' value greater than 70%, however for some reason this is proving
difficult. Ideally I would like a parameter that prompts the user to enter a
percentage value, however each time I enter a parameter or criteria and then
run the query, it prompts me to enter a parameter value for the 'Total Visit
Hours Completed'. Whether I enter a value or not, the query does not display
any records.

Anyone have any ideas as to why I keep getting prompted to enter 'Total
Visit Hours Completed' parameter, even though I have not entered this in the
criteria and how I can enter a criteria or parameter correctly to achieve the
above. Many thanks.

KR

Ammo
 
J

John Spencer

As a guess you need to repeat the entire calculation in the having clause.


PARAMETERS [Enter Percentage Cutoff] IEEEDouble;

SELECT school_details_tbl.CostCentre, school_details_tbl.SchoolName
, Sum(DateDiff("n",[VisitStartTime], [VisitFinishTime])/60) AS [Total Visit
Hours Completed]
, school_details_tbl.VisitHoursPurchased
, [Total Visit Hours Completed]/[VisitHoursPurchased] AS [% Visits Completed]
FROM school_details_tbl INNER JOIN visit_details_tbl ON
school_details_tbl.CostCentre = visit_details_tbl.CostCentre
GROUP BY school_details_tbl.CostCentre, school_details_tbl.SchoolName,
school_details_tbl.VisitHoursPurchased

HAVING Sum(DateDiff("n",[VisitStartTime], [VisitFinishTime])/60) /
[VisitHoursPurchased] >= [Enter Percentage Cutoff]

Make sure that you enter the percentage as a decimal value - 70% is .7.

Or if you just want to enter a whole number
= [Enter Percentage Cutoff]/100
Click to expand...

Or getting really tricky and allowing either a whole number or a decimal value
= IIF([Enter Percentage Cutoff]>1,[Enter Percentage Cutoff]/100
Click to expand...
,[Enter Percentage Cutoff])



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have created the following query:

SELECT school_details_tbl.CostCentre, school_details_tbl.SchoolName,
Sum(DateDiff("n",[VisitStartTime],[VisitFinishTime])/60) AS [Total Visit
Hours Completed], school_details_tbl.VisitHoursPurchased, [Total Visit Hours
Completed]/[VisitHoursPurchased] AS [% Visits Completed]
FROM school_details_tbl INNER JOIN visit_details_tbl ON
school_details_tbl.CostCentre = visit_details_tbl.CostCentre
GROUP BY school_details_tbl.CostCentre, school_details_tbl.SchoolName,
school_details_tbl.VisitHoursPurchased;

I am attempting to insert Criteria under the '% Visits Completed' expression
field I have created so that it only displays records that have a '% Visits
Completed' value greater than 70%, however for some reason this is proving
difficult. Ideally I would like a parameter that prompts the user to enter a
percentage value, however each time I enter a parameter or criteria and then
run the query, it prompts me to enter a parameter value for the 'Total Visit
Hours Completed'. Whether I enter a value or not, the query does not display
any records.

Anyone have any ideas as to why I keep getting prompted to enter 'Total
Visit Hours Completed' parameter, even though I have not entered this in the
criteria and how I can enter a criteria or parameter correctly to achieve the
above. Many thanks.

KR

Ammo
Click to expand...
 
A

Ammo

Hi John,

Thank you for your help on this!!!

John Spencer said:
As a guess you need to repeat the entire calculation in the having clause.


PARAMETERS [Enter Percentage Cutoff] IEEEDouble;

SELECT school_details_tbl.CostCentre, school_details_tbl.SchoolName
, Sum(DateDiff("n",[VisitStartTime], [VisitFinishTime])/60) AS [Total Visit
Hours Completed]
, school_details_tbl.VisitHoursPurchased
, [Total Visit Hours Completed]/[VisitHoursPurchased] AS [% Visits Completed]
FROM school_details_tbl INNER JOIN visit_details_tbl ON
school_details_tbl.CostCentre = visit_details_tbl.CostCentre
GROUP BY school_details_tbl.CostCentre, school_details_tbl.SchoolName,
school_details_tbl.VisitHoursPurchased

HAVING Sum(DateDiff("n",[VisitStartTime], [VisitFinishTime])/60) /
[VisitHoursPurchased] >= [Enter Percentage Cutoff]

Make sure that you enter the percentage as a decimal value - 70% is .7.

Or if you just want to enter a whole number
= [Enter Percentage Cutoff]/100
Click to expand...

Or getting really tricky and allowing either a whole number or a decimal value
= IIF([Enter Percentage Cutoff]>1,[Enter Percentage Cutoff]/100
Click to expand...
,[Enter Percentage Cutoff])



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have created the following query:

SELECT school_details_tbl.CostCentre, school_details_tbl.SchoolName,
Sum(DateDiff("n",[VisitStartTime],[VisitFinishTime])/60) AS [Total Visit
Hours Completed], school_details_tbl.VisitHoursPurchased, [Total Visit Hours
Completed]/[VisitHoursPurchased] AS [% Visits Completed]
FROM school_details_tbl INNER JOIN visit_details_tbl ON
school_details_tbl.CostCentre = visit_details_tbl.CostCentre
GROUP BY school_details_tbl.CostCentre, school_details_tbl.SchoolName,
school_details_tbl.VisitHoursPurchased;

I am attempting to insert Criteria under the '% Visits Completed' expression
field I have created so that it only displays records that have a '% Visits
Completed' value greater than 70%, however for some reason this is proving
difficult. Ideally I would like a parameter that prompts the user to enter a
percentage value, however each time I enter a parameter or criteria and then
run the query, it prompts me to enter a parameter value for the 'Total Visit
Hours Completed'. Whether I enter a value or not, the query does not display
any records.

Anyone have any ideas as to why I keep getting prompted to enter 'Total
Visit Hours Completed' parameter, even though I have not entered this in the
criteria and how I can enter a criteria or parameter correctly to achieve the
above. Many thanks.

KR

Ammo
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Value in Crosstab Query 4
Using IN expression with a parameter to filter queries 2
Parameter Query with DSum 1
Why am I asked to enter parameter value? 7
Parameter Values prompted for twice 5
Select Reccords based on Expression Results 3
Parameter query on expression field 2
Too Many Parameter Prompts 1

Top