Parameter/Criteria settings for percentage expression field

[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

 

[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

Or getting really tricky and allowing either a whole number or a decimal value

= IIF([Enter Percentage Cutoff]>1,[Enter Percentage Cutoff]/100

,[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

 

[Ammo]

Hi John,

Thank you for your help on this!!!

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

Or getting really tricky and allowing either a whole number or a decimal value

= IIF([Enter Percentage Cutoff]>1,[Enter Percentage Cutoff]/100

,[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