473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to filter on an expression in a report?

beacon
579 512MB
Hi everybody,

It's been a long day and, to make matters worse, I start my vacation as soon as I walk out the door tonight. That being said, my mental faculties aren't as sharp as they should be....so if this is something I should otherwise know, please forgive me.

I have a report that is based on a crosstab query. The crosstab query has a field called [Total by Form], which totals all occurrences of a form. On the report, I use the [Total by Form] with a field on a subreport to create a percentage expression, which I put in a text box called FormPercent.

I've now been given the task of giving the user the option on the form that's used to launch the report of filtering out all forms (i.e. rows on the report) that are less than 10%.

I've got the form setup to call the report and I pass the report an OpenArgs that tells the report whether or not to set the FilterOn property to 'Yes' or 'No'. Then, I was going to put my filter expression in the Filter property of the report so that it would trigger when the report was run and the user indicated that they wanted to view the exceptions (those forms/rows that are equal to or greater than 10%).

I'm having trouble with the filter expression though. Can I create a filter expression for an expression that is in the report or can I only create filter expressions for fields that are in the underlying query?

Just for reference, here's the expression in the Percent11 text box:
Expand|Select|Wrap|Line Numbers
  1. =IIf(([Col12]=0) And (srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total=0),0,IIf(([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total>0.00000001) And ([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total<0.05),([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total)+0.005,[Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total))
  2.  
If the answer above is the latter, is there any way I can accomplish what I'm trying to do without having to rewrite any of the queries?

Thanks,
beacon
Nov 4 '11 #1
10 5810
Stewart Ross
2,545 Expert Mod 2GB
Hi Beacon. When you apply a filter to a form or a report you are in effect applying a WHERE clause to the fields in the current recordsource of the form or the report, not the controls present on the form or report.

In your filter expression you are trying to refer to a field which appears to be from a report total. You cannot use this to filter the report itself. A total field is calculated as the sum of the values in the relevant field or fields in the current recordsource. Clearly, you cannot apply a value as a filter that has not yet been calculated at the time you apply the filter!

I would further comment that basing reports on crosstabs is problematic if you then have to refer to the pivoted fields in expressions of some kind - it leads to massive duplication of effort as you try to refer to values in many separately-named fields.

I would suggest instead that you would be better off looking at a different solution for the filtering, one that does not involve the report (because you cannot access a total until it has been calculated), or trying to access the pivoted fields of the crosstab query itself (because pivoting effectively unnormalises the data).

You could, for example, join your crosstab query to a separate query which calculates the group totals of the rows concerned. As these group totals would be repeated for each row within whatever grouping you have chosen you could then apply a suitable filter to the relevant rows of your report without having to go through the contortions you are trying at present.

-Stewart
Nov 5 '11 #2
beacon
579 512MB
Thanks for the reply Stewart.

I think I know the answer to my next question, but I'm going to ask it anyway to just to confirm.

If I keep the current setup of my report, can I add VBA behind the report to hide those rows where the value in the calculated field isn't greater than or equal to 10%?

My crosstabs have been great up to this point and I basically use them just to display the data that is slowly compiled in multiple other underlying queries. I've looked over these queries a good deal and I'm not sure I can setup the report the same way and include another report that calculates the percentage...this may be outside the realm of my intelligence/imagination. If I included the SQL for query that the report is based on and the four queries that make up the subreports that are included in the report, do you think you'd be able to make sense of it and give me some pointers?

Thanks for your help,
beacon
Nov 14 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi Beacon. It possible to use the On Format or On Print events to print rows selectively, but without seeing an example of your row data I am not sure if this would be a viable approach or not.

If you think how you would approach this problem in Excel, you would create a sum of whatever value it is you want to express as a percentage first, then divide the individual values by this sum to get the percentage. The same is true here.

You don't need a subreport to do this; what you need is either to include the relevant total in the crosstab itself, or to create a separate totals query and join it to the crosstab concerned.

Adding a row total to the rows of a crosstab is easy - take the crosstab's Value field expression and add the same expression as a row heading again within the crosstab under a new field name.

If you need a total that is at a different level of grouping than the individual crosstab rows it is still a straightforward task to create a suitable totals query which you can join to the crosstab concerned. If you could post the SQL for your PIVOT (crosstab) query and give an example of which field you need the total of I'd be happy to look at it for you and advise how it can be done.

-Stewart
Nov 16 '11 #4
beacon
579 512MB
The record source for the main report is qryTotalNonCompliantFormsSummary. The SQL for that query is:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(qryTotalNonCompliantFormsFormByPatient.Result) AS CountOfResult
  2. SELECT qryFormsActive.FormName, CStr(Forms!frmReportTotalNonCompliantFormsByProgram!BeginningDate) AS BeginningDate, CStr(Forms!frmReportTotalNonCompliantFormsByProgram!EndingDate) AS EndingDate, Count(qryTotalNonCompliantFormsFormByPatient.Result) AS [Total by Form]
  3. FROM qryFormsActive LEFT JOIN qryTotalNonCompliantFormsFormByPatient ON qryFormsActive.FormName = qryTotalNonCompliantFormsFormByPatient.FormName
  4. GROUP BY qryFormsActive.FormName, CStr(Forms!frmReportTotalNonCompliantFormsByProgram!BeginningDate), CStr(Forms!frmReportTotalNonCompliantFormsByProgram!EndingDate), Forms!frmReportTotalNonCompliantFormsByProgram!BeginningDate, Forms!frmReportTotalNonCompliantFormsByProgram!EndingDate
  5. PIVOT qryTotalNonCompliantFormsFormByPatient.ProgramInitials In ("AFP","BMTP","CAP","Competency","EEP","GPP","MDP","RIDR","SBP","SLRP");
  6.  
The numerator for my percentage comes from this query as [Total by Form].

There's also a subreport with a record source of
Expand|Select|Wrap|Line Numbers
  1. SELECT qryTotalNonCompliantFormsTotalsByProgram.TotalCharts, qryTotalNonCompliantFormsTotalsByProgram.Total, qryTotalNonCompliantFormsTotalsByProgram.AFP, qryTotalNonCompliantFormsTotalsByProgram.BMTP, qryTotalNonCompliantFormsTotalsByProgram.CAP, qryTotalNonCompliantFormsTotalsByProgram.Competency, qryTotalNonCompliantFormsTotalsByProgram.EEP, qryTotalNonCompliantFormsTotalsByProgram.GPP, qryTotalNonCompliantFormsTotalsByProgram.MDP, qryTotalNonCompliantFormsTotalsByProgram.RIDR, qryTotalNonCompliantFormsTotalsByProgram.SBP, qryTotalNonCompliantFormsTotalsByProgram.SLRP FROM qryTotalNonCompliantFormsTotalsByProgram; 
  2.  
The SQL for the qryTotalNonCompliantFormsTotalsByProgram is:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(qryTotalNonCompliantFormsTotalsByProgramDetail.Result) AS CountOfResult
  2. SELECT qryTotalNonCompliantFormsTotalsByProgramDetail.TotalCharts, Count(qryTotalNonCompliantFormsTotalsByProgramDetail.Result) AS Total
  3. FROM qryTotalNonCompliantFormsTotalsByProgramDetail
  4. WHERE (((qryTotalNonCompliantFormsTotalsByProgramDetail.DischargeDate) Between [Forms]![frmReportTotalNonCompliantFormsByProgram]![BeginningDate] And [Forms]![frmReportTotalNonCompliantFormsByProgram]![EndingDate]))
  5. GROUP BY qryTotalNonCompliantFormsTotalsByProgramDetail.TotalCharts
  6. PIVOT qryTotalNonCompliantFormsTotalsByProgramDetail.ProgramInitials In ("AFP","BMTP","CAP","Competency","EEP","GPP","MDP","RIDR","SBP","SLRP");
  7.  
The denominator for my percentage comes from [Total] in this query.

Finally, here's the control source for the field ([Percent11]) on the report that holds the percentage ([Col12] is the field that holds the value for [Total by Form] from the first query...there's also a bunch of added detail to ensure that the percentage gets rounded properly):
Expand|Select|Wrap|Line Numbers
  1. =IIf(([Col12]=0) And (srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total=0),0,IIf(([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total>0.00000001) And ([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total<0.05),([Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total)+0.005,[Col12]/srptTotalNonCompliantFormsByProgramTotalsByProgram.Report!Total))
  2.  
I had another project come up and only now had a chance to post this. Thanks for your help Stewart.

beacon
Dec 5 '11 #5
beacon
579 512MB
I think I've got it working now, but I'm curious about one thing. I added code to the OnFormat event and the report appears to be displaying the correct information for each row in the details section, as I intended. However, the report takes at least twice as long, if not much longer, to run now than it did before. Also, the titlebar in the VBA window tens to flicker back and forth like the code is still running.

Is this due to the fact that the report has to compile and run the code for each row in the detail section? I'm confused by this behavior because I already had code in the OnFormat event that would alternate the row color and that didn't seem to have any effect on the amount of time it took the report to run when it was added.
Dec 12 '11 #6
Stewart Ross
2,545 Expert Mod 2GB
Hi Beacon. It's difficult to comment without seeing the code concerned. Be aware that the On Format event can be fired several times for each row, as rows may be moved on the page depending on the content of the current and previous rows - so if you are performing something that involves multiple processes and screen interactions you may well get the delays and flickering you are experiencing.

You may also wish to see if some of the processing can be done in the On Print event (which only fires once for each detail row), but in On Print you do not have the option of changing the actual values displayed (which you can do with the Format event).

-Stewart
Dec 12 '11 #7
TheSmileyCoder
2,322 Expert Mod 2GB
Also be aware that some code will cause the format event to fired again. For instance changing the position/width/height of a textbox will cause the format event to fire again. In theory there is a formatcounter that counts the number of times an item has been formatted, but in my experience it was not reliable. If you want to check how many times each section has been formated, you could write it to the immediate pane using Debug.print, at least it might give you an idea of how big the problem is.
Dec 12 '11 #8
beacon
579 512MB
Ok...last issue and I can stick a fork in this turkey and call it a day. The report is acting as I need it to, but if the condition is met that is supposed to hide the rows I mentioned in my other posts, I need for the last page to also be suppressed.

Is there any way to do this? I tried formatting all of the sections that could possible appear on the last page and setting the Visible property to False, but that only made the last page blank.

Here's the code for the entire report if you think it will help:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Private shadeNextRow As Boolean
  5.  
  6.     Const shadedColor = 12632256
  7.     Const normalColor = 16777215
  8.  
  9.     Dim blnCancel As Boolean
  10.  
  11. '------------------------------------------------------------
  12. ' Detail_Format
  13. '
  14. ' This subroutine formats the detail section by alternating
  15. ' the row color
  16. '------------------------------------------------------------
  17. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  18.  
  19.     Dim temp As Integer
  20.  
  21.     If (Me.OpenArgs = True) And (Page = Pages) Then
  22.         Me.Section(acDetail).Visible = False
  23.     Else
  24.         Me.Section(acDetail).Visible = True
  25.     End If
  26.  
  27.     If Me.OpenArgs = True Then
  28.         If Me.Percent11 < 0.1 Then
  29.             Me.Section(acDetail).Visible = False
  30.             temp = 1
  31.         Else
  32.             Me.Section(acDetail).Visible = True
  33.             temp = 0
  34.             If shadeNextRow = True Then
  35.                 Me.Section(acDetail).BackColor = shadedColor
  36.             Else
  37.                 Me.Section(acDetail).BackColor = normalColor
  38.             End If
  39.         End If
  40.     Else
  41.         temp = 0
  42.         If shadeNextRow = True Then
  43.             Me.Section(acDetail).BackColor = shadedColor
  44.         Else
  45.             Me.Section(acDetail).BackColor = normalColor
  46.         End If
  47.     End If
  48.  
  49.     If temp = 0 Then
  50.         shadeNextRow = Not shadeNextRow
  51.     End If
  52.  
  53. End Sub
  54.  
  55. '------------------------------------------------------------
  56. ' PageHeaderSection_Format
  57. '
  58. ' This subroutine formats the page header section by
  59. ' determining whether or not to display specific sections
  60. ' of the report or not
  61. '------------------------------------------------------------
  62. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  63.  
  64.     If (Me.OpenArgs = True) And (Page = Pages) Then
  65.         Me.Section(acPageHeader).Visible = False
  66.     ElseIf Page = Pages Then
  67.         Me.Section(acPageHeader).Visible = False
  68.     Else
  69.         Me.Section(acPageHeader).Visible = True
  70.     End If
  71.  
  72. End Sub
  73.  
  74. '------------------------------------------------------------
  75. ' PageFooterSection_Format
  76. '
  77. ' This subroutine formats the page footer section by
  78. ' hiding it if the report is for exceptions only
  79. '------------------------------------------------------------
  80. Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
  81.  
  82.     If (Me.OpenArgs = True) And (Page = Pages) Then
  83.         Me.Section(acPageFooter).Visible = False
  84.     Else
  85.         Me.Section(acPageFooter).Visible = True
  86.     End If
  87.  
  88. End Sub
  89.  
  90. '------------------------------------------------------------
  91. ' Report_Open
  92. '
  93. ' This subroutine accepts an OpenArgs to determine which
  94. ' textbox to display in the page header
  95. '------------------------------------------------------------
  96. Private Sub Report_Open(Cancel As Integer)
  97.  
  98.     Dim varOpenArg
  99.  
  100.     If Me.OpenArgs = True Then
  101.         Me.pageHeaderOn.Visible = True
  102.         Me.reportHeaderOn.Visible = True
  103.         Me.pageHeaderOff.Visible = False
  104.         Me.reportHeaderOff.Visible = False
  105.     Else
  106.         Me.pageHeaderOn.Visible = False
  107.         Me.reportHeaderOn.Visible = False
  108.         Me.pageHeaderOff.Visible = True
  109.         Me.reportHeaderOff.Visible = True
  110.     End If
  111.  
  112. End Sub
  113.  
  114. '------------------------------------------------------------
  115. ' Report_Close
  116. '
  117. ' This subroutine prompts the user to determine whether or
  118. ' not to close the report and return to the Switchboard
  119. '------------------------------------------------------------
  120. Private Sub Report_Close()
  121.  
  122.     If blnCancel = True Then
  123.         Exit Sub
  124.     End If
  125.  
  126.     Dim userSelection
  127.  
  128.     Beep
  129.     userSelection = MsgBox("Would you like to run another report?", vbQuestion + vbYesNo, "Confirm")
  130.         If (userSelection = vbYes) Then
  131.             DoCmd.Restore
  132.             Forms!frmReportTrends.Visible = True
  133.             'OpenReportSelect
  134.         ElseIf (userSelection = vbNo) Then
  135.             MsgBox "You will now be returned to the Main Menu", vbOKOnly + vbInformation, "Return to Main Menu"
  136.             DoCmd.Restore
  137.             DoCmd.Close acForm, "frmReportTrends"
  138.             OpenSwitch
  139.         End If
  140.  
  141. ExitReport:
  142.     Exit Sub
  143.  
  144. End Sub
  145.  
  146. '------------------------------------------------------------
  147. ' Report_NoData
  148. '
  149. ' This subroutine informs the user that no data is available
  150. ' for the report and returns the user to
  151. ' frmReportTrends
  152. '------------------------------------------------------------
  153. Private Sub Report_NoData(Cancel As Integer)
  154.  
  155.     Dim userResponse As Integer
  156.  
  157.     Cancel = True
  158.     blnCancel = True
  159.  
  160.     userResponse = MsgBox("The report returned no data", vbInformation + vbOKOnly, "Report: No Data")
  161.  
  162.     OpenReportSelect
  163.  
  164. End Sub
  165.  
  166. '------------------------------------------------------------
  167. ' CloseMe
  168. '
  169. ' This subroutine closes the current report
  170. '------------------------------------------------------------
  171. Private Sub CloseMe()
  172.  
  173.     DoCmd.Close acReport, Me.Name
  174.  
  175. End Sub
  176.  
  177. '------------------------------------------------------------
  178. ' OpenReportSelect
  179. '
  180. ' This subroutine opens frmReportTrends
  181. '------------------------------------------------------------
  182. Private Sub OpenReportSelect()
  183.  
  184.     DoCmd.OpenForm "frmReportTrends"
  185.  
  186. End Sub
  187.  
  188. '------------------------------------------------------------
  189. ' OpenSwitch
  190. '
  191. ' This subroutine opens the Switchboard
  192. '------------------------------------------------------------
  193. Private Sub OpenSwitch()
  194.  
  195.     DoCmd.OpenForm "Switchboard"
  196.  
  197. End Sub
  198.  
By the way, I ended up finding out that we were experiencing some unusual network issues when I last posted. Now that we got those issues resolved, the report is processing at a much more acceptable rate.

Thanks,
beacon
Dec 15 '11 #9
TheSmileyCoder
2,322 Expert Mod 2GB
Have you tried cancelling (Cancel=true) the format of the section instead of setting its visibility to false?
Dec 15 '11 #10
beacon
579 512MB
I hadn't tried it before you asked Smiley, but I've since tried it and, unfortunately, it didn't work.

However, I was able to make it work using the following code that sets the ForceNewPage property to "None", sets the visibility property to false, and cancels the formatting in the report footer (I'm including all code, but the only code I changed is in the ReportFooter_Format event if anyone wants to skip to that area of code):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Private shadeNextRow As Boolean
  5.  
  6.     Const shadedColor = 12632256
  7.     Const normalColor = 16777215
  8.  
  9.     Dim blnCancel As Boolean
  10.  
  11. '------------------------------------------------------------
  12. ' Detail_Format
  13. '
  14. ' This subroutine formats the detail section by alternating
  15. ' the row color
  16. '------------------------------------------------------------
  17. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  18.  
  19.     Dim temp As Integer
  20.  
  21.     If (Me.OpenArgs = True) And (Page = Pages) Then
  22.         Me.Section(acDetail).Visible = False
  23.     Else
  24.         Me.Section(acDetail).Visible = True
  25.     End If
  26.  
  27.     If Me.OpenArgs = True Then
  28.         If Me.Percent11 < 0.1 Then
  29.             Me.Section(acDetail).Visible = False
  30.             temp = 1
  31.         Else
  32.             Me.Section(acDetail).Visible = True
  33.             temp = 0
  34.             If shadeNextRow = True Then
  35.                 Me.Section(acDetail).BackColor = shadedColor
  36.             Else
  37.                 Me.Section(acDetail).BackColor = normalColor
  38.             End If
  39.         End If
  40.     Else
  41.         temp = 0
  42.         If shadeNextRow = True Then
  43.             Me.Section(acDetail).BackColor = shadedColor
  44.         Else
  45.             Me.Section(acDetail).BackColor = normalColor
  46.         End If
  47.     End If
  48.  
  49.     If temp = 0 Then
  50.         shadeNextRow = Not shadeNextRow
  51.     End If
  52.  
  53. End Sub
  54.  
  55. '------------------------------------------------------------
  56. ' PageHeaderSection_Format
  57. '
  58. ' This subroutine formats the page header section by
  59. ' determining whether or not to display specific sections
  60. ' of the report or not
  61. '------------------------------------------------------------
  62. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  63.  
  64.     If (Me.OpenArgs = True) And (Page = Pages) Then
  65.         Me.Section(acPageHeader).Visible = False
  66.     ElseIf Page = Pages Then
  67.         Me.Section(acPageHeader).Visible = False
  68.     Else
  69.         Me.Section(acPageHeader).Visible = True
  70.     End If
  71.  
  72. End Sub
  73.  
  74. '------------------------------------------------------------
  75. ' PageFooterSection_Format
  76. '
  77. ' This subroutine formats the page footer section by
  78. ' hiding it if the report is for exceptions only
  79. '------------------------------------------------------------
  80. Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
  81.  
  82.     If (Me.OpenArgs = True) And (Page = Pages) Then
  83.         Me.Section(acPageFooter).Visible = False
  84.     Else
  85.         Me.Section(acPageFooter).Visible = True
  86.     End If
  87.  
  88. End Sub
  89.  
  90. '------------------------------------------------------------
  91. ' Report_Open
  92. '
  93. ' This subroutine accepts an OpenArgs to determine whether
  94. ' or not to filter out rows with zero deficiencies
  95. '------------------------------------------------------------
  96. Private Sub Report_Open(Cancel As Integer)
  97.  
  98.     Dim varOpenArg
  99.  
  100.     If Me.OpenArgs = True Then
  101.         'Me.FilterOn = True
  102.         Me.pageHeaderOn.Visible = True
  103.         Me.reportHeaderOn.Visible = True
  104.         Me.pageHeaderOff.Visible = False
  105.         Me.reportHeaderOff.Visible = False
  106.     Else
  107.         'Me.FilterOn = False
  108.         Me.pageHeaderOn.Visible = False
  109.         Me.reportHeaderOn.Visible = False
  110.         Me.pageHeaderOff.Visible = True
  111.         Me.reportHeaderOff.Visible = True
  112.     End If
  113.  
  114. End Sub
  115.  
  116. '------------------------------------------------------------
  117. ' Report_Close
  118. '
  119. ' This subroutine prompts the user to determine whether or
  120. ' not to close the report and return to the Switchboard
  121. '------------------------------------------------------------
  122. Private Sub Report_Close()
  123.  
  124.     If blnCancel = True Then
  125.         Exit Sub
  126.     End If
  127.  
  128.     Dim userSelection
  129.  
  130.     Beep
  131.     userSelection = MsgBox("Would you like to run another report?", vbQuestion + vbYesNo, "Confirm")
  132.         If (userSelection = vbYes) Then
  133.             DoCmd.Restore
  134.             Forms!frmReportTrends.Visible = True
  135.             'OpenReportSelect
  136.         ElseIf (userSelection = vbNo) Then
  137.             MsgBox "You will now be returned to the Main Menu", vbOKOnly + vbInformation, "Return to Main Menu"
  138.             DoCmd.Restore
  139.             DoCmd.Close acForm, "frmReportTrends"
  140.             OpenSwitch
  141.         End If
  142.  
  143. ExitReport:
  144.     Exit Sub
  145.  
  146. End Sub
  147.  
  148. '------------------------------------------------------------
  149. ' Report_NoData
  150. '
  151. ' This subroutine informs the user that no data is available
  152. ' for the report and returns the user to
  153. ' frmReportTrends
  154. '------------------------------------------------------------
  155. Private Sub Report_NoData(Cancel As Integer)
  156.  
  157.     Dim userResponse As Integer
  158.  
  159.     Cancel = True
  160.     blnCancel = True
  161.  
  162.     userResponse = MsgBox("The report returned no data", vbInformation + vbOKOnly, "Report: No Data")
  163.  
  164.     OpenReportSelect
  165.  
  166. End Sub
  167.  
  168. '------------------------------------------------------------
  169. ' CloseMe
  170. '
  171. ' This subroutine closes the current report
  172. '------------------------------------------------------------
  173. Private Sub CloseMe()
  174.  
  175.     DoCmd.Close acReport, Me.Name
  176.  
  177. End Sub
  178.  
  179. '------------------------------------------------------------
  180. ' OpenReportSelect
  181. '
  182. ' This subroutine opens frmReportTrends
  183. '------------------------------------------------------------
  184. Private Sub OpenReportSelect()
  185.  
  186.     DoCmd.OpenForm "frmReportTrends"
  187.  
  188. End Sub
  189.  
  190. '------------------------------------------------------------
  191. ' OpenSwitch
  192. '
  193. ' This subroutine opens the Switchboard
  194. '------------------------------------------------------------
  195. Private Sub OpenSwitch()
  196.  
  197.     DoCmd.OpenForm "Switchboard"
  198.  
  199. End Sub
  200.  
  201. '------------------------------------------------------------
  202. ' ReportFooter_Format
  203. '
  204. ' This subroutine formats the report footer section by
  205. ' hiding it if the report is for exceptions only
  206. '------------------------------------------------------------
  207. Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
  208.  
  209.     If Me.OpenArgs = True Then
  210.         Me.Section(acFooter).ForceNewPage = 0   '0 = None
  211.         Me.Section(acFooter).Visible = False
  212.         Cancel = True
  213.     End If
  214.  
  215. End Sub
  216.  
A big thanks to everyone for getting me on the right track.

beacon
Dec 19 '11 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
1
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used...
30
by: ljungers | last post by:
Anyone have an idea how I can filter a report that I'm outputting to Word. Using the following 2 Command to print and output to word. The strWhere is a list of selected items/rows to print, and...
38
by: bdockery | last post by:
One to Many relationship: Manufactuerer to Model Manufactuerer table has , Products Table has , ,
4
by: Fran | last post by:
I recently tried to use code for "Use a multi-select list box to filter a report" from Allen Browne in my database. I was able to add the code and adapt it to my needs, however I am getting an...
5
by: wbosw | last post by:
I'm trying to filter the datatable at runtime from data selected from dropdown listboxes and a texbox. I place the values into variables and use the variables in the filter expression. However,...
5
by: DAHMB | last post by:
Hi all, Using Access 2007 I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows: SELECT tblSundaySchoolAttendance.StudentID,...
3
by: Gord | last post by:
I'm trying to filter a report with a date in VB code. If I type an actual date bracketed with the pound symbol (i.e. #3-Jul-08#) I can get the filter to work. I can't seem to get it to work by...
1
by: spyka | last post by:
I have issue regarding paging using filter expression. Here's the piece of code for filtering: if ( $q->param("Filter") ) { $Id=$q->param('User_Id'); ...
0
by: husin | last post by:
Hi all, It seems like every one here a is a VB person :P But I was wondering, how do I filter crystal report by TextBoxes and ListBoxes on a single page, all coming from SQL database. Like,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.