How do I transfer or writing of this

[azhar2006]

Hello Guys
How do I transfer or writing of this

SELECT Consumin_Report.Unity_Name, Sum(Consumin_Report.[No]) AS SumOfNo
FROM Consumin_Report


GROUP BY Consumin_Report.Unity_Name;
In a field in report
I want to collect the numbers for a particular substance in the report

[ItsMe]

If you are using Print Preview or trying to print a report, you will need to retrieve data before the report is rendered. The report needs to collect all information before it can render the result and send it to the printer.

Use the On Open or On Load events to do "things" before the report renders.

If I need to collect data that is not included in the report's recordset, I will use DAO in the On Load event. I will store the results in variables declared in the Reports Header. This way, the variables are available everywhere within the Report's VBA module.

However, I do not believe an entire Dataset from a SELECT query will be useful in the On Open or On Load events. It would be difficult to store the results in variables for easier use. It will probably be more feasible to use a subquery that incorporates your SELECT query.

[azhar2006]

Thank you, Professor
I had used this expression in the appended report
= DCount ("*"; "QueryName"; "(Field1Name= 'colonel') And Field2Name= 'angel' ")

But this report collects the number of fields
And I want to collect figures

[ItsMe]

Domain aggregate functions are good for retrieving a single variable from a domain such as a table or a query. This would be appropriate for the On Load event. Store the result in a variable Private to the Report's Module.

If you want to retrieve a value from a field, you could use Dlookup.
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

Possible Solution:
You could create a Named Saved Query object using the SQL you previously posted. With that, you could use DLookup to retrieve the value from the field, "SumOfNo". Use the name of your query object as the Domain argument for the Dlookup function.

[azhar2006]

How to create a Named Saved Query object using the SQL you previously posted. With that, you could use DLookup to retrieve the value from the field, "SumOfNo". Use the name of your query object as the Domain argument for the Dlookup function.

[ItsMe]

How to create a Named Saved Query object using the SQL you previously posted...

You have done this before, I am sure. Create a new query using the query designer/builder. The designer should open in Design View. Go to SQL view. Paste your SQL in the SQL window. Name and Save your object.

[azhar2006]

Yes, I worked , professor
But I want to write phrase SQL editor written in VBA or in unstructured text box on the report

[ItsMe]

...
But I want to write phrase SQL editor written in VBA or in unstructured text box on the report

I could be wrong but, I believe the only way to get this to work within a Report is to use another Query Object. The key factor here is the GROUP BY Clause. So you would have a subreport depend on the Query Object or you could substitute the SubReport with the VBA (DLookup, Variables, text box control). The reason being, prior to the report rendering, all data needs to be retrieved and held in memory.

The only other way to avoid creating a Query Object (that I can imagine) may be to place your SQL in a function in a standard module. However, I am not seeing an advantage to this over a Query object. Just remember to get your Sum() into the Report's Module before the Report renders.