Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 10th, 2003, 03:35 AM
ppenn
Guest
 
Posts: n/a
Default Formatting reports

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
 
Old September 10th, 2003, 07:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
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
 
Old September 10th, 2003, 07:51 AM
ppenn
Guest
 
Posts: n/a
Default

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
 
Old September 10th, 2003, 12:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
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
 
Old September 10th, 2003, 01:11 PM
ppenn
Guest
 
Posts: n/a
Default

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
 
Old September 10th, 2003, 01:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
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
 
Old September 10th, 2003, 03:24 PM
ppenn
Guest
 
Posts: n/a
Default

Hello Greg sounds good to me - I will give it a try tomorrow - thank you very much for your patience and help Regards Peter
 
Old September 12th, 2003, 08:40 AM
ppenn
Guest
 
Posts: n/a
Default

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:(
 
Old September 17th, 2003, 02:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
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
 
Old September 17th, 2003, 02:40 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
comparision among crystal reports and Data reports avats Crystal Reports 0 April 13th, 2005 01:01 PM
How to create/design reports with crystal reports BassmaniaQ Crystal Reports 0 December 14th, 2004 04:58 AM
Formatting Crystal Reports field/Box objects svnrao Crystal Reports 1 September 23rd, 2004 09:08 AM
Reports Manager does not list reports hinesweb BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 25th, 2004 04:39 AM
formatting reports clueless_may Access VBA 1 May 14th, 2004 01:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.