Frontpage results - Number rounding issue

Syn1962 · Jul 7, 2008

I BELIEVE I DID IT!......I removed the Null stuff cause I don't need that
anyways and
it works! YEAH!!!
Thanks so much for your help....I couldn't have figured this out without
you....now I have a lot of
work to do to change all these queries.....oh well...job security :-)

Here's what worked for me

It's done as an Expression.....

SELECT Round(Avg([2008survey].[BS_Comfort_Temperature_Air_Flow]),1) AS
BS_Comfort
FROM 2008survey;

This gave me the 2.9 number I was looking for!

Thanks Bunches for your help!

Syn1962 said:
I guess not, it may be because I'm using a query not a table as the below
statement appears to be a table...or it's just me
I've created another query using only one of the items. I keep coming up
with errors...What am
I writing incorrectly?

Here's the basic query without selecting it to do an average:
SELECT [2008survey].BS_Comfort_Temperature_Air_Flow
FROM 2008survey;

I rewrote it per your instructions to read:
SELECT
Round(Avg(IIf([2008survey].BS_Comfort_Temperature_Air_Flow="N",Null,([2008survey].BS_Comfort_Temperature_Air_Flow))),2) AS BS_Comfort_Temperature_Air_Flow
FROM 2008survey;

and received this error......
Data type mismatch in criteria expression.

Maybe I'm not putting in something around the query part of the expression?

Can you tell what I'm doing incorrectly?????

Kathleen Anderson said:

This Access SQL does Averaging and Rounding and sets the decimal places to
2.

SELECT Round(Avg(IIf([Call_Center]="N",Null,([Call_Center]))),2) AS
Call_CenterRank,
Round(Avg(IIf([Surgery_Scheduling]="N",Null,Round([Surgery_Scheduling]))),2)
AS Surgery_SchedulingRank
FROM Results;

Does this help?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

Kathleen Anderson said:

I think I have some SQL at home that will help - give me a few hours.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

here ya go
--------------------------------
SELECT [2008survey].Facility,
Avg([2008survey].BS_Comfort_Temperature_Air_Flow) AS
AvgOfBS_Comfort_Temperature_Air_Flow,
Avg([2008survey].BS_Performance_of_Routine_Cleaning_Tasks) AS
AvgOfBS_Performance_of_Routine_Cleaning_Tasks,
Avg([2008survey].BS_Elevator_Services) AS AvgOfBS_Elevator_Services,
Avg([2008survey].BS_Interior_Maintenance_and_Repairs) AS
AvgOfBS_Interior_Maintenance_and_Repairs,
Avg([2008survey].BS_Exterior_Grounds_Maintenance) AS
AvgOfBS_Exterior_Grounds_Maintenance,
Avg([2008survey].BS_Parking_Adequacy_and_Availability) AS
AvgOfBS_Parking_Adequacy_and_Availability,
Avg([2008survey].BS_Recycling_and_Rubbish_Removal) AS
AvgOfBS_Recycling_and_Rubbish_Removal, Avg([2008survey].BS_Security) AS
AvgOfBS_Security, Avg([2008survey].BS_Emergency_Preparedness) AS
AvgOfBS_Emergency_Preparedness,
Avg([2008survey].BS_Overall_Opinion_of_Building_Services) AS
AvgOfBS_Overall_Opinion_of_Building_Services,
Avg([2008survey].BAI_Building_Exterior_Repair_and_Maintenance) AS
AvgOfBAI_Building_Exterior_Repair_and_Maintenance,
Avg([2008survey].BAI_Parking_Landscaping_Appearance) AS
AvgOfBAI_Parking_Landscaping_Appearance,
Avg([2008survey].BAI_General_Quality_and_Condition_of_Office_Areas) AS
AvgOfBAI_General_Quality_and_Condition_of_Office_Areas,
Avg([2008survey].BAI_Condition_of_Your_Individual_Work_Station) AS
AvgOfBAI_Condition_of_Your_Individual_Work_Station,
Avg([2008survey].BAI_Quality_and_Condition_Conference_Meeting_Rooms) AS
AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms,
Avg([2008survey].BAI_Quality_and_Condition_Common_Areas) AS
AvgOfBAI_Quality_and_Condition_Common_Areas,
Avg([2008survey].BAI_Quality_and_Condition_of_Restrooms) AS
AvgOfBAI_Quality_and_Condition_of_Restrooms,
Avg([2008survey].BAI_Suitability_of_Facility_to_Conduct_Meetings) AS
AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings,
Avg([2008survey].BAI_Overall_Opinion_of_Building_Appearance_Image) AS
AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image,
Avg([2008survey].BSS_Dining_Services) AS AvgOfBSS_Dining_Services,
Avg([2008survey].BSS_Health_and_Fitness_Facility) AS
AvgOfBSS_Health_and_Fitness_Facility,
Avg([2008survey].BSS_Mailing_Services)
AS AvgOfBSS_Mailing_Services,
Avg([2008survey].BSS_Package_Shipping_Receiving) AS
AvgOfBSS_Package_Shipping_Receiving,
Avg([2008survey].BSS_Occupational_Health_and_Safety_Mgmt_Services) AS
AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services,
Avg([2008survey].BSS_Vending_Area) AS AvgOfBSS_Vending_Area,
Avg([2008survey].BSS_Overall_Opinion_of_Business_Support_Services) AS
AvgOfBSS_Overall_Opinion_of_Business_Support_Services,
Avg([2008survey].CRE_FMP_Communication_of_Building_Procedures) AS
AvgOfCRE_FMP_Communication_of_Building_Procedures,
Avg([2008survey].CRE_FMP_Effective_Response_to_Service_Calls_and_Requests)
AS
AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests,
Avg([2008survey].CRE_FMP_Delivery_on_Commitments) AS
AvgOfCRE_FMP_Delivery_on_Commitments,
Avg([2008survey].CRE_FMP_Overall_Opinion_of_FMS_Performance) AS
AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance
FROM 2008survey
GROUP BY [2008survey].Facility;
-------------------------------------------------

:

Can you post the SQL from your Access Query here?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

The complete survey is too large, below is the beginning of it and a
couple
lines of the survey.

I've created a facility survey, when the survey is taken the
information
will go into the Access table. I have created queries to obtain
averages
in
Access because I haven't found a way to get FP to do them. So then I
created
reports in FP to pull specific facilities...ie OH8099 display the
information.
What I got from your prior postings is that it is not possible to
display
this information correctly (by writing in script in SQL) if you are
using
queries in Access to get your results in FP.
---------------------------------

fp_sQry="SELECT * FROM 2008LocationAvg WHERE (Facility LIKE
'%OH8099%')"
fp_sDefault=""
fp_sNoRecords="OH8099"
fp_sDataConn="FacilitySurvey"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="Facility"
fp_sMenuValue="Facility"

Click to expand...

Click to expand...

fp_sColTypes="&Facility=202&AvgOfBS_Comfort_Temperature_Air_Flow=5&AvgOfBS_Performance_of_Routine_Cleaning_Tasks=5&AvgOfBS_Elevator_Services=5&AvgOfBS_Interior_Maintenance_and_Repairs=5&AvgOfBS_Exterior_Grounds_Maintenance=5&AvgOfBS_Parking_Adequacy_and_Availability=5&AvgOfBS_Recycling_and_Rubbish_Removal=5&AvgOfBS_Security=5&AvgOfBS_Emergency_Preparedness=5&AvgOfBS_Overall_Opinion_of_Building_Services=5&AvgOfBAI_Building_Exterior_Repair_and_Maintenance=5&AvgOfBAI_Parking_Landscaping_Appearance=5&AvgOfBAI_General_Quality_and_Condition_of_Office_Areas=5&AvgOfBAI_Condition_of_Your_Individual_Work_Station=5&AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms=5&AvgOfBAI_Quality_and_Condition_Common_Areas=5&AvgOfBAI_Quality_and_Condition_of_Restrooms=5&AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings=5&AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image=5&AvgOfBSS_Dining_Services=5&AvgOfBSS_Health_and_Fitness_Facility=5&AvgOfBSS_Mailing_Services=5&AvgOfBSS_Package_Shipping_Receiving=5&AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services=5&AvgOfBSS_Vending_Area=5&AvgOfBSS_Overall_Opinion_of_Business_Support_Services=5&AvgOfCRE_FMP_Communication_of_Building_Procedures=5&AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests=5&AvgOfCRE_FMP_Delivery_on_Commitments=5&AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance=5&"s-columnnames="Facility,AvgOfBS_Comfort_Temperature_Air_Flow,AvgOfBS_Performance_of_Routine_Cleaning_Tasks,AvgOfBS_Elevator_Services,AvgOfBS_Interior_Maintenance_and_Repairs,AvgOfBS_Exterior_Grounds_Maintenance,AvgOfBS_Parking_Adequacy_and_Availability,AvgOfBS_Recycling_and_Rubbish_Removal,AvgOfBS_Security,AvgOfBS_Emergency_Preparedness,AvgOfBS_Overall_Opinion_of_Building_Services,AvgOfBAI_Building_Exterior_Repair_and_Maintenance,AvgOfBAI_Parking_Landscaping_Appearance,AvgOfBAI_General_Quality_and_Condition_of_Office_Areas,AvgOfBAI_Condition_of_Your_Individual_Work_Station,AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms,AvgOfBAI_Quality_and_Condition_Common_Areas,AvgOfBAI_Quality_and_Condition_of_Restrooms,AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings,AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image,AvgOfBSS_Dining_Services,AvgOfBSS_Health_and_Fitness_Facility,AvgOfBSS_Mailing_Services,AvgOfBSS_Package_Shipping_Receiving,AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services,AvgOfBSS_Vending_Area,AvgOfBSS_Overall_Opinion_of_Business_Support_Services,AvgOfCRE_FMP_Communication_of_Building_Procedures,AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests,AvgOfCRE_FMP_Delivery_on_Commitments,AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance"s-columnnames="Facility,AvgOfBS_Comfort_Temperature_Air_Flow,AvgOfBS_Performance_of_Routine_Cleaning_Tasks,AvgOfBS_Elevator_Services,AvgOfBS_Interior_Maintenance_and_Repairs,AvgOfBS_Exterior_Grounds_Maintenance,AvgOfBS_Parking_Adequacy_and_Availability,AvgOfBS_Recycling_and_Rubbish_Removal,AvgOfBS_Security,AvgOfBS_Emergency_Preparedness,AvgOfBS_Overall_Opinion_of_Building_Services,AvgOfBAI_Building_Exterior_Repair_and_Maintenance,AvgOfBAI_Parking_Landscaping_Appearance,AvgOfBAI_General_Quality_and_Condition_of_Office_Areas,AvgOfBAI_Condition_of_Your_Individual_Work_Station,AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms,AvgOfBAI_Quality_and_Condition_Common_Areas,AvgOfBAI_Quality_and_Condition_of_Restrooms,AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings,AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image,AvgOfBSS_Dining_Services,AvgOfBSS_Health_and_Fitness_Facility,AvgOfBSS_Mailing_Services,AvgOfBSS_Package_Shipping_Receiving,AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services,AvgOfBSS_Vending_Area,AvgOfBSS_Overall_Opinion_of_Business_Support_Services,AvgOfCRE_FMP_Communication_of_Building_Procedures,AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests,AvgOfCRE_FMP_Delivery_on_Commitments,AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance"

Kathleen Anderson · Jul 7, 2008

That's great! Just so you know, the NULL is there to not count records that
have no entry for that field, so it won't skew your average.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

Syn1962 said:
I BELIEVE I DID IT!......I removed the Null stuff cause I don't need that
anyways and
it works! YEAH!!!
Thanks so much for your help....I couldn't have figured this out without
you....now I have a lot of
work to do to change all these queries.....oh well...job security
Here's what worked for me

It's done as an Expression.....

SELECT Round(Avg([2008survey].[BS_Comfort_Temperature_Air_Flow]),1) AS
BS_Comfort
FROM 2008survey;

This gave me the 2.9 number I was looking for!

Thanks Bunches for your help!

Syn1962 said:

I guess not, it may be because I'm using a query not a table as the below
statement appears to be a table...or it's just me
I've created another query using only one of the items. I keep coming up
with errors...What am
I writing incorrectly?

Here's the basic query without selecting it to do an average:
SELECT [2008survey].BS_Comfort_Temperature_Air_Flow
FROM 2008survey;

I rewrote it per your instructions to read:
SELECT
Round(Avg(IIf([2008survey].BS_Comfort_Temperature_Air_Flow="N",Null,([2008survey].BS_Comfort_Temperature_Air_Flow))),2)
AS BS_Comfort_Temperature_Air_Flow
FROM 2008survey;

and received this error......
Data type mismatch in criteria expression.

Maybe I'm not putting in something around the query part of the
expression?

Can you tell what I'm doing incorrectly?????

Kathleen Anderson said:

This Access SQL does Averaging and Rounding and sets the decimal places
to
2.

SELECT Round(Avg(IIf([Call_Center]="N",Null,([Call_Center]))),2) AS
Call_CenterRank,
Round(Avg(IIf([Surgery_Scheduling]="N",Null,Round([Surgery_Scheduling]))),2)
AS Surgery_SchedulingRank
FROM Results;

Does this help?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

I think I have some SQL at home that will help - give me a few hours.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

here ya go
--------------------------------
SELECT [2008survey].Facility,
Avg([2008survey].BS_Comfort_Temperature_Air_Flow) AS
AvgOfBS_Comfort_Temperature_Air_Flow,
Avg([2008survey].BS_Performance_of_Routine_Cleaning_Tasks) AS
AvgOfBS_Performance_of_Routine_Cleaning_Tasks,
Avg([2008survey].BS_Elevator_Services) AS AvgOfBS_Elevator_Services,
Avg([2008survey].BS_Interior_Maintenance_and_Repairs) AS
AvgOfBS_Interior_Maintenance_and_Repairs,
Avg([2008survey].BS_Exterior_Grounds_Maintenance) AS
AvgOfBS_Exterior_Grounds_Maintenance,
Avg([2008survey].BS_Parking_Adequacy_and_Availability) AS
AvgOfBS_Parking_Adequacy_and_Availability,
Avg([2008survey].BS_Recycling_and_Rubbish_Removal) AS
AvgOfBS_Recycling_and_Rubbish_Removal, Avg([2008survey].BS_Security)
AS
AvgOfBS_Security, Avg([2008survey].BS_Emergency_Preparedness) AS
AvgOfBS_Emergency_Preparedness,
Avg([2008survey].BS_Overall_Opinion_of_Building_Services) AS
AvgOfBS_Overall_Opinion_of_Building_Services,
Avg([2008survey].BAI_Building_Exterior_Repair_and_Maintenance) AS
AvgOfBAI_Building_Exterior_Repair_and_Maintenance,
Avg([2008survey].BAI_Parking_Landscaping_Appearance) AS
AvgOfBAI_Parking_Landscaping_Appearance,
Avg([2008survey].BAI_General_Quality_and_Condition_of_Office_Areas)
AS
AvgOfBAI_General_Quality_and_Condition_of_Office_Areas,
Avg([2008survey].BAI_Condition_of_Your_Individual_Work_Station) AS
AvgOfBAI_Condition_of_Your_Individual_Work_Station,
Avg([2008survey].BAI_Quality_and_Condition_Conference_Meeting_Rooms)
AS
AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms,
Avg([2008survey].BAI_Quality_and_Condition_Common_Areas) AS
AvgOfBAI_Quality_and_Condition_Common_Areas,
Avg([2008survey].BAI_Quality_and_Condition_of_Restrooms) AS
AvgOfBAI_Quality_and_Condition_of_Restrooms,
Avg([2008survey].BAI_Suitability_of_Facility_to_Conduct_Meetings) AS
AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings,
Avg([2008survey].BAI_Overall_Opinion_of_Building_Appearance_Image)
AS
AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image,
Avg([2008survey].BSS_Dining_Services) AS AvgOfBSS_Dining_Services,
Avg([2008survey].BSS_Health_and_Fitness_Facility) AS
AvgOfBSS_Health_and_Fitness_Facility,
Avg([2008survey].BSS_Mailing_Services)
AS AvgOfBSS_Mailing_Services,
Avg([2008survey].BSS_Package_Shipping_Receiving) AS
AvgOfBSS_Package_Shipping_Receiving,
Avg([2008survey].BSS_Occupational_Health_and_Safety_Mgmt_Services)
AS
AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services,
Avg([2008survey].BSS_Vending_Area) AS AvgOfBSS_Vending_Area,
Avg([2008survey].BSS_Overall_Opinion_of_Business_Support_Services)
AS
AvgOfBSS_Overall_Opinion_of_Business_Support_Services,
Avg([2008survey].CRE_FMP_Communication_of_Building_Procedures) AS
AvgOfCRE_FMP_Communication_of_Building_Procedures,
Avg([2008survey].CRE_FMP_Effective_Response_to_Service_Calls_and_Requests)
AS
AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests,
Avg([2008survey].CRE_FMP_Delivery_on_Commitments) AS
AvgOfCRE_FMP_Delivery_on_Commitments,
Avg([2008survey].CRE_FMP_Overall_Opinion_of_FMS_Performance) AS
AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance
FROM 2008survey
GROUP BY [2008survey].Facility;
-------------------------------------------------

:

Can you post the SQL from your Access Query here?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

The complete survey is too large, below is the beginning of it
and a
couple
lines of the survey.

I've created a facility survey, when the survey is taken the
information
will go into the Access table. I have created queries to obtain
averages
in
Access because I haven't found a way to get FP to do them. So
then I
created
reports in FP to pull specific facilities...ie OH8099 display the
information.
What I got from your prior postings is that it is not possible to
display
this information correctly (by writing in script in SQL) if you
are
using
queries in Access to get your results in FP.
---------------------------------

fp_sQry="SELECT * FROM 2008LocationAvg WHERE (Facility LIKE
'%OH8099%')"
fp_sDefault=""
fp_sNoRecords="OH8099"
fp_sDataConn="FacilitySurvey"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="Facility"
fp_sMenuValue="Facility"

Click to expand...

fp_sColTypes="&Facility=202&AvgOfBS_Comfort_Temperature_Air_Flow=5&AvgOfBS_Performance_of_Routine_Cleaning_Tasks=5&AvgOfBS_Elevator_Services=5&AvgOfBS_Interior_Maintenance_and_Repairs=5&AvgOfBS_Exterior_Grounds_Maintenance=5&AvgOfBS_Parking_Adequacy_and_Availability=5&AvgOfBS_Recycling_and_Rubbish_Removal=5&AvgOfBS_Security=5&AvgOfBS_Emergency_Preparedness=5&AvgOfBS_Overall_Opinion_of_Building_Services=5&AvgOfBAI_Building_Exterior_Repair_and_Maintenance=5&AvgOfBAI_Parking_Landscaping_Appearance=5&AvgOfBAI_General_Quality_and_Condition_of_Office_Areas=5&AvgOfBAI_Condition_of_Your_Individual_Work_Station=5&AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms=5&AvgOfBAI_Quality_and_Condition_Common_Areas=5&AvgOfBAI_Quality_and_Condition_of_Restrooms=5&AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings=5&AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image=5&AvgOfBSS_Dining_Services=5&AvgOfBSS_Health_and_Fitness_Facility=5&AvgOfBSS_Mailing_Services=5&AvgOfBSS_Package_Shipping_Receiving=5&AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services=5&AvgOfBSS_Vending_Area=5&AvgOfBSS_Overall_Opinion_of_Business_Support_Services=5&AvgOfCRE_FMP_Communication_of_Building_Procedures=5&AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests=5&AvgOfCRE_FMP_Delivery_on_Commitments=5&AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance=5&"

fp_iDisplayCols=31
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>

<table
BORDER="1" style="border-left-width: 0px; border-right-width:
0px;
border-top-width: 0px">
<tr>
<td nowrap valign="top" style="border-style: none;
border-width: medium" align="center">

OH8099</td>
</tr>
<tr>
<td nowrap valign="top" style="border-left-style: none;
border-left-width: medium; border-right-style: none;
border-right-width:
medium; border-top-style: none; border-top-width: medium;
border-bottom-style: solid; border-bottom-width: 1px">
</td>
</tr>
<tr>
<td align="center">
<%=FP_FieldVal(fp_rs,"AvgOfBS_Comfort_Temperature_Air_Flow")%></td>
</tr>
<tr>
<td align="center">
<%=FP_FieldVal(fp_rs,"AvgOfBS_Performance_of_Routine_Cleaning_Tasks")%></td>
</tr>
<tr>
-------------------------------------------

:

Can you post the SQL from your query here?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

Kathleen,
I am having the same problem as well, I'm using a query to
extract
results
that I need to show in percentages correctly!
I created them in Front Page however, I also have Expression
Web,
any
chance
I can get the results to show correctly using EP? I've tried
to
find
it
myself but so far I've had no luck in finding a solution to
this
anywhere.

Your help is greatly appreciated.

:

If you are using an Access Query as your DRW Recordsource (as
a
'View'),
you
can't modify the SQL, as far as I know. Can you copy the
Access
Query
SQL
into the Custom Query window of a new DRW and change it
there?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
FrontPage Support: http://www.frontpagemvps.com/

Hi Kathleen, thanks for the reply, Im stilll not qite there
Im
afraid.
I opened the "Custom Query" and added the second line thus:

SELECT * FROM clientswherefind

Click to expand...

Click to expand...

Stefan B Rusynko · Jul 8, 2008

That is why it throws an error - Null values can't be rounded
- and conditional IIF statements (although they work) are not fully supported in ASP

Better to exclude empty fields from the whole process using a criteria as in

SELECT Round(Avg([2008survey].[BS_Comfort_Temperature_Air_Flow]),1) AS
BS_Comfort FROM 2008survey WHERE 2008survey].[BS_Comfort_Temperature_Air_Flow] <>"" ;

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________

| That's great! Just so you know, the NULL is there to not count records that
| have no entry for that field, so it won't skew your average.
|
| --
|
| ~ Kathleen Anderson
| Microsoft MVP - FrontPage
| Spider Web Woman Designs
| Expression Web Resources: http://www.spiderwebwoman.com/xweb/
| FrontPage Resources: http://www.spiderwebwoman.com/resources/
| Please reply to the newsgroup for the benefit of others
|
|
| | >I BELIEVE I DID IT!......I removed the Null stuff cause I don't need that
| > anyways and
| > it works! YEAH!!!
| > Thanks so much for your help....I couldn't have figured this out without
| > you....now I have a lot of
| > work to do to change all these queries.....oh well...job security :-)

| > Here's what worked for me
| >
| > It's done as an Expression.....
| >
| > SELECT Round(Avg([2008survey].[BS_Comfort_Temperature_Air_Flow]),1) AS
| > BS_Comfort
| > FROM 2008survey;
| >
| > This gave me the 2.9 number I was looking for!
| >
| > Thanks Bunches for your help!
| >
| >
| > "Syn1962" wrote:
| >
| >> I guess not, it may be because I'm using a query not a table as the below
| >> statement appears to be a table...or it's just me :-)

| >> I've created another query using only one of the items. I keep coming up
| >> with errors...What am
| >> I writing incorrectly?
| >>
| >> Here's the basic query without selecting it to do an average:
| >> SELECT [2008survey].BS_Comfort_Temperature_Air_Flow
| >> FROM 2008survey;
| >>
| >>
| >> I rewrote it per your instructions to read:
| >> SELECT
| >> Round(Avg(IIf([2008survey].BS_Comfort_Temperature_Air_Flow="N",Null,([2008survey].BS_Comfort_Temperature_Air_Flow))),2)
| >> AS BS_Comfort_Temperature_Air_Flow
| >> FROM 2008survey;
| >>
| >> and received this error......
| >> Data type mismatch in criteria expression.
| >>
| >> Maybe I'm not putting in something around the query part of the
| >> expression?
| >>
| >> Can you tell what I'm doing incorrectly?????
| >>
| >>
| >>
| >> "Kathleen Anderson" wrote:
| >>
| >> > This Access SQL does Averaging and Rounding and sets the decimal places
| >> > to
| >> > 2.
| >> >
| >> > SELECT Round(Avg(IIf([Call_Center]="N",Null,([Call_Center]))),2) AS
| >> > Call_CenterRank,
| >> > Round(Avg(IIf([Surgery_Scheduling]="N",Null,Round([Surgery_Scheduling]))),2)
| >> > AS Surgery_SchedulingRank
| >> > FROM Results;
| >> >
| >> > Does this help?
| >> >
| >> >
| >> > --
| >> >
| >> > ~ Kathleen Anderson
| >> > Microsoft MVP - FrontPage
| >> > Spider Web Woman Designs
| >> > Expression Web Resources: http://www.spiderwebwoman.com/xweb/
| >> > FrontPage Resources: http://www.spiderwebwoman.com/resources/
| >> > Please reply to the newsgroup for the benefit of others
| >> >
| >> >
| >> > | >> > >I think I have some SQL at home that will help - give me a few hours.
| >> > >
| >> > > --
| >> > >
| >> > > ~ Kathleen Anderson
| >> > > Microsoft MVP - FrontPage
| >> > > Spider Web Woman Designs
| >> > > Expression Web Resources: http://www.spiderwebwoman.com/xweb/
| >> > > FrontPage Resources: http://www.spiderwebwoman.com/resources/
| >> > > Please reply to the newsgroup for the benefit of others
| >> > >
| >> > >
| >> > >
| >> > > | >> > >> here ya go
| >> > >> --------------------------------
| >> > >> SELECT [2008survey].Facility,
| >> > >> Avg([2008survey].BS_Comfort_Temperature_Air_Flow) AS
| >> > >> AvgOfBS_Comfort_Temperature_Air_Flow,
| >> > >> Avg([2008survey].BS_Performance_of_Routine_Cleaning_Tasks) AS
| >> > >> AvgOfBS_Performance_of_Routine_Cleaning_Tasks,
| >> > >> Avg([2008survey].BS_Elevator_Services) AS AvgOfBS_Elevator_Services,
| >> > >> Avg([2008survey].BS_Interior_Maintenance_and_Repairs) AS
| >> > >> AvgOfBS_Interior_Maintenance_and_Repairs,
| >> > >> Avg([2008survey].BS_Exterior_Grounds_Maintenance) AS
| >> > >> AvgOfBS_Exterior_Grounds_Maintenance,
| >> > >> Avg([2008survey].BS_Parking_Adequacy_and_Availability) AS
| >> > >> AvgOfBS_Parking_Adequacy_and_Availability,
| >> > >> Avg([2008survey].BS_Recycling_and_Rubbish_Removal) AS
| >> > >> AvgOfBS_Recycling_and_Rubbish_Removal, Avg([2008survey].BS_Security)
| >> > >> AS
| >> > >> AvgOfBS_Security, Avg([2008survey].BS_Emergency_Preparedness) AS
| >> > >> AvgOfBS_Emergency_Preparedness,
| >> > >> Avg([2008survey].BS_Overall_Opinion_of_Building_Services) AS
| >> > >> AvgOfBS_Overall_Opinion_of_Building_Services,
| >> > >> Avg([2008survey].BAI_Building_Exterior_Repair_and_Maintenance) AS
| >> > >> AvgOfBAI_Building_Exterior_Repair_and_Maintenance,
| >> > >> Avg([2008survey].BAI_Parking_Landscaping_Appearance) AS
| >> > >> AvgOfBAI_Parking_Landscaping_Appearance,
| >> > >> Avg([2008survey].BAI_General_Quality_and_Condition_of_Office_Areas)
| >> > >> AS
| >> > >> AvgOfBAI_General_Quality_and_Condition_of_Office_Areas,
| >> > >> Avg([2008survey].BAI_Condition_of_Your_Individual_Work_Station) AS
| >> > >> AvgOfBAI_Condition_of_Your_Individual_Work_Station,
| >> > >> Avg([2008survey].BAI_Quality_and_Condition_Conference_Meeting_Rooms)
| >> > >> AS
| >> > >> AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms,
| >> > >> Avg([2008survey].BAI_Quality_and_Condition_Common_Areas) AS
| >> > >> AvgOfBAI_Quality_and_Condition_Common_Areas,
| >> > >> Avg([2008survey].BAI_Quality_and_Condition_of_Restrooms) AS
| >> > >> AvgOfBAI_Quality_and_Condition_of_Restrooms,
| >> > >> Avg([2008survey].BAI_Suitability_of_Facility_to_Conduct_Meetings) AS
| >> > >> AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings,
| >> > >> Avg([2008survey].BAI_Overall_Opinion_of_Building_Appearance_Image)
| >> > >> AS
| >> > >> AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image,
| >> > >> Avg([2008survey].BSS_Dining_Services) AS AvgOfBSS_Dining_Services,
| >> > >> Avg([2008survey].BSS_Health_and_Fitness_Facility) AS
| >> > >> AvgOfBSS_Health_and_Fitness_Facility,
| >> > >> Avg([2008survey].BSS_Mailing_Services)
| >> > >> AS AvgOfBSS_Mailing_Services,
| >> > >> Avg([2008survey].BSS_Package_Shipping_Receiving) AS
| >> > >> AvgOfBSS_Package_Shipping_Receiving,
| >> > >> Avg([2008survey].BSS_Occupational_Health_and_Safety_Mgmt_Services)
| >> > >> AS
| >> > >> AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services,
| >> > >> Avg([2008survey].BSS_Vending_Area) AS AvgOfBSS_Vending_Area,
| >> > >> Avg([2008survey].BSS_Overall_Opinion_of_Business_Support_Services)
| >> > >> AS
| >> > >> AvgOfBSS_Overall_Opinion_of_Business_Support_Services,
| >> > >> Avg([2008survey].CRE_FMP_Communication_of_Building_Procedures) AS
| >> > >> AvgOfCRE_FMP_Communication_of_Building_Procedures,
| >> > >> Avg([2008survey].CRE_FMP_Effective_Response_to_Service_Calls_and_Requests)
| >> > >> AS
| >> > >> AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests,
| >> > >> Avg([2008survey].CRE_FMP_Delivery_on_Commitments) AS
| >> > >> AvgOfCRE_FMP_Delivery_on_Commitments,
| >> > >> Avg([2008survey].CRE_FMP_Overall_Opinion_of_FMS_Performance) AS
| >> > >> AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance
| >> > >> FROM 2008survey
| >> > >> GROUP BY [2008survey].Facility;
| >> > >> -------------------------------------------------
| >> > >>
| >> > >> "Kathleen Anderson" wrote:
| >> > >>
| >> > >>> Can you post the SQL from your Access Query here?
| >> > >>>
| >> > >>> --
| >> > >>>
| >> > >>> ~ Kathleen Anderson
| >> > >>> Microsoft MVP - FrontPage
| >> > >>> Spider Web Woman Designs
| >> > >>> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
| >> > >>> FrontPage Resources: http://www.spiderwebwoman.com/resources/
| >> > >>> Please reply to the newsgroup for the benefit of others
| >> > >>>
| >> > >>>
| >> > >>>
| >> > >>> | >> > >>> > The complete survey is too large, below is the beginning of it
| >> > >>> > and a
| >> > >>> > couple
| >> > >>> > lines of the survey.
| >> > >>> >
| >> > >>> > I've created a facility survey, when the survey is taken the
| >> > >>> > information
| >> > >>> > will go into the Access table. I have created queries to obtain
| >> > >>> > averages
| >> > >>> > in
| >> > >>> > Access because I haven't found a way to get FP to do them. So
| >> > >>> > then I
| >> > >>> > created
| >> > >>> > reports in FP to pull specific facilities...ie OH8099 display the
| >> > >>> > information.
| >> > >>> > What I got from your prior postings is that it is not possible to
| >> > >>> > display
| >> > >>> > this information correctly (by writing in script in SQL) if you
| >> > >>> > are
| >> > >>> > using
| >> > >>> > queries in Access to get your results in FP.
| >> > >>> > ---------------------------------
| >> > >>> >
| >> > >>> >
| >> > >>> > fp_sQry="SELECT * FROM 2008LocationAvg WHERE (Facility LIKE
| >> > >>> > '%OH8099%')"
| >> > >>> > fp_sDefault=""
| >> > >>> > fp_sNoRecords="OH8099"
| >> > >>> > fp_sDataConn="FacilitySurvey"
| >> > >>> > fp_iMaxRecords=0
| >> > >>> > fp_iCommandType=1
| >> > >>> > fp_iPageSize=0
| >> > >>> > fp_fTableFormat=False
| >> > >>> > fp_fMenuFormat=False
| >> > >>> > fp_sMenuChoice="Facility"
| >> > >>> > fp_sMenuValue="Facility"
| >> > >>> >
| >> > >>
| >>
| >
fp_sColTypes="&Facility=202&AvgOfBS_Comfort_Temperature_Air_Flow=5&AvgOfBS_Performance_of_Routine_Cleaning_Tasks=5&AvgOfBS_Elevator_Services=5&AvgOfBS_Interior_Maintenance_and_Repairs=5&AvgOfBS_Exterior_Grounds_Maintenance=5&AvgOfBS_Parking_Adequacy_and_Availability=5&AvgOfBS_Recycling_and_Rubbish_Removal=5&AvgOfBS_Security=5&AvgOfBS_Emergency_Preparedness=5&AvgOfBS_Overall_Opinion_of_Building_Services=5&AvgOfBAI_Building_Exterior_Repair_and_Maintenance=5&AvgOfBAI_Parking_Landscaping_Appearance=5&AvgOfBAI_General_Quality_and_Condition_of_Office_Areas=5&AvgOfBAI_Condition_of_Your_Individual_Work_Station=5&AvgOfBAI_Quality_and_Condition_Conference_Meeting_Rooms=5&AvgOfBAI_Quality_and_Condition_Common_Areas=5&AvgOfBAI_Quality_and_Condition_of_Restrooms=5&AvgOfBAI_Suitability_of_Facility_to_Conduct_Meetings=5&AvgOfBAI_Overall_Opinion_of_Building_Appearance_Image=5&AvgOfBSS_Dining_Services=5&AvgOfBSS_Health_and_Fitness_Facility=5&AvgOfBSS_Mailing_Services=5&AvgOfBSS_Package_Shipping_Receiving=5&AvgOfBSS_Occupational_Health_and_Safety_Mgmt_Services=5&AvgOfBSS_Vending_Area=5&AvgOfBSS_Overall_Opinion_of_Business_Support_Services=5&AvgOfCRE_FMP_Communication_of_Building_Procedures=5&AvgOfCRE_FMP_Effective_Response_to_Service_Calls_and_Requests=5&AvgOfCRE_FMP_Delivery_on_Commitments=5&AvgOfCRE_FMP_Overall_Opinion_of_FMS_Performance=5&"
| >> > >>> > fp_iDisplayCols=31
| >> > >>> > fp_fCustomQuery=False
| >> > >>> > BOTID=1
| >> > >>> > fp_iRegion=BOTID
| >> > >>> > %>
| >> > >>> > 
| >> > >>> > <table
| >> > >>> > BORDER="1" style="border-left-width: 0px; border-right-width:
| >> > >>> > 0px;
| >> > >>> > border-top-width: 0px">
| >> > >>> > <tr>
| >> > >>> > <td nowrap valign="top" style="border-style: none;
| >> > >>> > border-width: medium" align="center">
| >> > >>> > 
| >> > >>> > OH8099</td>
| >> > >>> > </tr>
| >> > >>> > <tr>
| >> > >>> > <td nowrap valign="top" style="border-left-style: none;
| >> > >>> > border-left-width: medium; border-right-style: none;
| >> > >>> > border-right-width:
| >> > >>> > medium; border-top-style: none; border-top-width: medium;
| >> > >>> > border-bottom-style: solid; border-bottom-width: 1px">
| >> > >>> > </td>
| >> > >>> > </tr>
| >> > >>> > <tr>
| >> > >>> > <td align="center">
| >> > >>> > <%=FP_FieldVal(fp_rs,"AvgOfBS_Comfort_Temperature_Air_Flow")%></td>
| >> > >>> > </tr>
| >> > >>> > <tr>
| >> > >>> > <td align="center">
| >> > >>> > <%=FP_FieldVal(fp_rs,"AvgOfBS_Performance_of_Routine_Cleaning_Tasks")%></td>
| >> > >>> > </tr>
| >> > >>> > <tr>
| >> > >>> > -------------------------------------------
| >> > >>> >
| >> > >>> >
| >> > >>> > "Kathleen Anderson" wrote:
| >> > >>> >
| >> > >>> >> Can you post the SQL from your query here?
| >> > >>> >>
| >> > >>> >>
| >> > >>> >> --
| >> > >>> >>
| >> > >>> >> ~ Kathleen Anderson
| >> > >>> >> Microsoft MVP - FrontPage
| >> > >>> >> Spider Web Woman Designs
| >> > >>> >> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
| >> > >>> >> FrontPage Resources: http://www.spiderwebwoman.com/resources/
| >> > >>> >> Please reply to the newsgroup for the benefit of others
| >> > >>> >>
| >> > >>> >>
| >> > >>> >> | >> > >>> >> > Kathleen,
| >> > >>> >> > I am having the same problem as well, I'm using a query to
| >> > >>> >> > extract
| >> > >>> >> > results
| >> > >>> >> > that I need to show in percentages correctly!
| >> > >>> >> > I created them in Front Page however, I also have Expression
| >> > >>> >> > Web,
| >> > >>> >> > any
| >> > >>> >> > chance
| >> > >>> >> > I can get the results to show correctly using EP? I've tried
| >> > >>> >> > to
| >> > >>> >> > find
| >> > >>> >> > it
| >> > >>> >> > myself but so far I've had no luck in finding a solution to
| >> > >>> >> > this
| >> > >>> >> > anywhere.
| >> > >>> >> >
| >> > >>> >> > Your help is greatly appreciated.
| >> > >>> >> >
| >> > >>> >> > "Kathleen Anderson [MVP - FrontPage]" wrote:
| >> > >>> >> >
| >> > >>> >> >> If you are using an Access Query as your DRW Recordsource (as
| >> > >>> >> >> a
| >> > >>> >> >> 'View'),
| >> > >>> >> >> you
| >> > >>> >> >> can't modify the SQL, as far as I know. Can you copy the
| >> > >>> >> >> Access
| >> > >>> >> >> Query
| >> > >>> >> >> SQL
| >> > >>> >> >> into the Custom Query window of a new DRW and change it
| >> > >>> >> >> there?
| >> > >>> >> >>
| >> > >>> >> >> --
| >> > >>> >> >>
| >> > >>> >> >> ~ Kathleen Anderson
| >> > >>> >> >> Microsoft MVP - FrontPage
| >> > >>> >> >> Spider Web Woman Designs
| >> > >>> >> >> web: http://www.spiderwebwoman.com/resources/
| >> > >>> >> >> FrontPage Support: http://www.frontpagemvps.com/
| >> > >>> >> >>
| >> > >>> >> >>
| >> > >>> >> >>
| >> > >>> >> >>
| >> > >>> >> >> | >> > >>> >> >> > Hi Kathleen, thanks for the reply, Im stilll not qite there
| >> > >>> >> >> > Im
| >> > >>> >> >> > afraid.
| >> > >>> >> >> > I opened the "Custom Query" and added the second line thus:
| >> > >>> >> >> >
| >> > >>> >> >> > SELECT * FROM clientswherefind
|
|

Frontpage results - Number rounding issue

Syn1962

Kathleen Anderson

Stefan B Rusynko