SQL Formating 1

[cneill]

Hi Everyone,

Following on from mt earler post, I would now like some help on formating the SQL

strSql = "SELECT Scheme, Sum(InternalSale) AS 'Internal Sale', Sum(Delivered) AS 'Total CERT Delivered' FROM QryElectricalFundingData"

To get Internal Sale with the space showing in the List Box I need to put it in Single Quotes 'Internal Sale' but the quotes show in the list box, is there a way to not show the quotes and keep the space?
I have one field in the Query that is a number that represents a percentage, so I would like to show the Average I tried Avg(PG) AS PG but that returned no results

Also I would like to Format the Total Cert Delivered
MajP kindly suggested format(Sum(Delivered),"#,###") AS TotalDelivered
so inserted into SQL

strSql = "SELECT Scheme, Sum(InternalSale) AS 'Internal Sale', format(Sum(Delivered),"#,###") AS 'Total CERT Delivered' FROM QryElectricalFundingData"
This gives me a Compile Error expected end of statement at the first #

Can anyone give me some quidance please?

Thanks cneill

 

[dhookom]

You can't use Sum() without a GROUP BY. Try:

strSql = "SELECT Scheme, Sum(InternalSale) AS [Internal Sale], format(Sum(Delivered),'#,###') AS [Total CERT Delivered] " & _
   "FROM QryElectricalFundingData GROUP BY Scheme ORDER BY Scheme"

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

I tried Avg(PG) AS PG but that returned no results

How about...
Average(PG) As Something

Randy

 

[cneill]

Hi dhookom,

Sorry the rest of the code is

strWhere = Me.Filter
'Build the listbox sql
strSql = "SELECT Scheme, Format(Sum(InternalSale),'#,###') AS [Internal Sale], Format(Sum(Delivered),'#,###') AS [Total CERT Delivered], Format(Avg(PG), '#.##') AS [PG %], FROM QryElectricalFundingData"
'stick the filter in the sql
strSql = strSql & " WHERE" & strWhere
strSql = strSql & "GROUP BY Scheme"
'reset the listbox filter
Me.ListBoxResults.RowSource = strSql

I have added your formating in which works so many thanks I now want to add in something like this
(Sum(Delivered)*Avg(PG))/100 AS Result but it is returning no results so I am doing something wrong can you help?
Thanks
CNEILL

 

[randy700]

OOPS -- Please disregard my reply.
Average is an Excel function.

Randy

 

[dhookom]

CNEILL,
Did you attempt to add a breakpoint to your code so it would stop running and you could debug.print the strSQL to paste into a blank query sql view?

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi dhookham,

Sorted it the final working code is

strSql = "SELECT Scheme, Format(Sum(InternalSale),'#,###') AS [Internal Sale], Format(Sum(Delivered),'#,###') AS [Total CERT Delivered], Format(Avg(PG), '0.00') AS [PG %],Format(([Total CERT Delivered]*[PG %])/100,'#,##') AS Result FROM QryElectricalFundingData"

Thanks for all your help

cneill