Hi, I have a report that performs a number of calculations in the format event, the report is derived from a query that also performs some calculations. The end result is a profit/loss figure. What I need to do is present the report with the highest loss figures first. Because the final calculation is performed in the format event how can I make the records sort accordingly.
Thanks in advance Peter

Since you said your report is based on a query, why not put all of your calculations on the query and sort by the appropriate columns there? Then make sure the report has no sorts in it that would override the query.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I have tried that but because the calculations were so complicated it was easier and more accurate to do it the way I have. I would presume from your reply that it is not possible to sort any other way
Peter

 Oh, not at all; I was just opting for the easiest route. You can sort the report by (in design mode) creating your field of interest on the report, then clicking VIEW > SORTING and GROUPING. A dialog box will open. Pick the fields you want to sort by.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hello Greg - I am sorry if I may sound awkward - but this report has to be calculated in the format event because very simply it looks at 84 different values and compares them to the elapsed time in months since a contract started and uses this amount in the calculation- unfortunately the final field that shows profit or loss is unbound so the sort cannot be applied by the method you suggest , but thanks for your suggestions. I think that I might be snookered - back to the drawing board!!!!!!!!
Regards
Peter

Oh, my!

Well, how about having the form calculate the totals on its event procedures? Then you can write the desired fields and the total to a temporary table. Base the report on the temporary table and sort the report by the desired (presaved) field in the manner in which I mentioned previously.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hello Greg sounds good to me - I will give it a try tomorrow - thank you very much for your patience and help Regards Peter

Does anyone know if the data, generated by a report as code executes in the format event, can be written to a temporary table
Peter:(

Peter, you still got it backwards. A report does not generate data. It merely reports on it. You should generate the data (and its table) from the form and then simply open a report based on the output table. Forms are active; reports are passive.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Actually, you can on an OnActivate event of the report, run a function which will create a recordset, append it to a table and then report against the table.

Here's an example:

        Set db = CurrentDb()
        Set rst = db.OpenRecordset(SourceSt, dbOpenDynaset)

        On Error GoTo ErrorReturn

        rst.MoveLast

        SqlString = "Select * from NewTableName where 1=2"

        Set Crst = db.OpenRecordset(SqlString, dbOpenDynaset)

        rst.MoveFirst

        For i = 1 To rst.RecordCount
        Crst.AddNew
            For j = 0 To Crst.Fields.Count - 1
                If Crst(j).Name = "USERNAME" Then
                    Crst(j).Value = CurrentUser()
                Else
                    Crst(j).Value = rst(j).Value
                End If
            Next
        Crst.Update
        rst.MoveNext

        Next
        DoCmd.OpenReport RptName, acPreview, SourceSt
Regards,

Beth M