|
Subject:
|
Formatting reports
|
|
Posted By:
|
ppenn
|
Post Date:
|
9/10/2003 3:35:26 AM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
9/10/2003 7:26:58 AM
|
quote:
Originally posted by ppenn
how can I make the records sort accordingly.
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/10/2003 7:51:16 AM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
9/10/2003 12:32:18 PM
|
quote:
Originally posted by ppenn
I would presume from your reply that it is not possible to sort any other way
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/10/2003 1:11:54 PM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
9/10/2003 1:39:36 PM
|
quote:
Originally posted by ppenn
unfortunately the final field that shows profit or loss is unbound so the sort cannot be applied by the method you suggest
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/10/2003 3:24:04 PM
|
Hello Greg sounds good to me - I will give it a try tomorrow - thank you very much for your patience and help Regards Peter
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/12/2003 8:40:38 AM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
9/17/2003 2:27:25 PM
|
quote:
Originally posted by ppenn
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, 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
|
|
Reply By:
|
BethMoffitt
|
Reply Date:
|
9/17/2003 2:40:25 PM
|
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/20/2003 6:07:48 PM
|
I would like to thank all who helped on this topic, a combination of all replies and quite a few late nights have solved the problem and I now have the result I wanted
Thanks again
Peter
|
