locked
Advice on Generating a Report with Multiple Subreports Using User-Selected Criteria from a Form RRS feed

  • Question

  • I know there are a ton of ways to do this, but it seems like every time I make a small change to my way (in order to eliminate potential errors from user input) I feel the need to change my approach. I'm hoping someone here might have some advice on the best way generate an Invoice Report. I have a database that keeps track of Billable hours, Expenses, and Payments in respective tables, and each of those are related to a Matters table. I would like users to be able to generate custom invoices according to the following criteria: 

    1. Select a Matter to invoice
    2. Choose whether they would like the Invoice report to include subreports for Billable Hours, Expenses, and Payments
    3. Select a date range for each of the subreports Billable Hours, Expenses, and Payments

    Here's my current approach: I have an Invoice Report that is based on a query, which takes its parameters from an input form. All of the subreports are also based on queries that take their parameters from the input form. The input form has text boxes for "From" and "To" dates, and a combobox that takes its RecordSource from a query (qryMatters). User selects a Matter, inputs dates, and then clicks a command button "View Report". The OnClick event for the command button opens the main invoice report: DoCmd.OpenReport ("InvoiceWithSubreportsByMatter"), acViewPreview, , {Criteria is what I'm having trouble with}. The RecordSource for the main invoice report is the same qryMatters as the search form combobox. However, all of the subreports are based on separate queries (e.g., qryBillables, qryExpenses, qryPayments), but they include a MatterID that I have linked to the main report Master-Child property. All of the queries work just fine when I type the parameters manually. But when I use the form to open the Invoice Report none of my subreports show up.

    I'm sure there's an easy fix for the missing subreports, but what I'm hoping is that someone can explain a good approach for generating these types of reports/invoices, or point me to a resource that lays things out. Any advice from the community would be greatly appreciated. 

    Monday, March 6, 2017 4:01 PM

Answers

  • To declare parameters you can either do so via the parameters dialogue in query design view, or by adding a PARAMETERS clause to the start of the query in SQL view:

    PARAMETERS [Forms]![frmCreateInvoice]![txtInvoiceFromDate] DATETIME,
    [Forms]![frmCreateInvoice]![txtInvoiceToDate] DATE TIME;
    SELECT etc.

    As regards the expressions for the WhereCondition argument, if the data type of the column in question is text then the value needs to be wrapped in literal quotes characters.  Because the quotes character is the delimiter for a text expression, you cannot simply put a quotes character within the expression, but a pair of contiguous quotes characters within an expression is interpreted as a literal quotes character, e.g.

    "SomeColumn = """ & somevariable & """"

    You could also use the single quote character:

    "SomeColumn = '" & somevariable & "'"

    but this would fail with values which include an apostrophe, like my own name in its original non-anglicized form, Cináed O'Siridean.

    If the data type is a number, as in your case, the value does not need to be wrapped in literal quotes characters, e.g.

    "SomeColumn = " & somevariable

    With date/time values, the delimiter for a date literal is the # character and the value has to be in US short date format or an otherwise internationally unambiguous format such as the ISO standard for date/time notation of YYYY-MM-DD hh:mm:ss (the standard uses case to distinguish between months and minutes, whereas Access uses nn for the latter), e.g.

    "SomeDateTimeColumn = #" & Format(somedatetimevariable, "yyyy-mm-dd hh:nn:ss") & "#"



    Ken Sheridan, Stafford, England

    • Marked as answer by le_kai Wednesday, March 15, 2017 4:08 AM
    Tuesday, March 7, 2017 4:19 PM

All replies

  • Hi,

    What exactly do you mean by "typing" the parameters manually? Was it a prompt? If you're referring to the Criteria row of the query, then perhaps you have an incorrect syntax when using the form. Can you show us the Criteria row? Thanks.

    Monday, March 6, 2017 4:26 PM
  • I note that the parameters for restricting the subreports define a date range.  Have you declared the parameters as DATETIME in the queries?  If date parameters are not declared, it is not unknown for them to be misinterpreted as arithmetical expressions.  This will not raise an error as the result of the expressions will be a number, and Access implements date/time values as a 64 bit floating point number, so there will be no data type mismatch.  A date expressed in a short date format will usually evaluate  as an arithmetical expression to a very small fractional number, which will correspond to a date/time around the end of the 19th century.  It's unlikely you'd have any row with such dates, so the query will return no rows.

    This might not be the cause of the problem of course, but a very similar problem was reported in the MS Access Community forum only last week, and was corrected once the parameters were declared.

    You say you are having problems with the WhereCondition argument of the OpenReport method.  This implies that you are not restricting the parent report by parameters, in which case the string expression for the WhereCondition argument would, if you are filtering the parent report on a text column, be something like this:

        "Matter = """ & Me.cboMatter & """"

    and like this if you are doing so on a numeric column, i.e. the combo box's BoundColumn is a hidden numeric key:

        "MatterID = " & Me.cboMatter


    Ken Sheridan, Stafford, England



    Monday, March 6, 2017 6:35 PM
  • @theDBGuy -- re: your question: I'm referring to a parameter criteria in the query. When I run the query manually (i.e., directly clicking 'run' inside the query itself) a dialog box pops up asking for the parameters, which I've set in the Criteria to take its argument from my form cboMatterID (see below). I do that sometimes to try and isolate my problems, checking to make sure the query is working properly and supplying my Invoice Report with the proper records.

    Here is the Criteria for filtering qryMatterID: [Forms]![frmCreateInvoice]![cboSelectInvoiceMatter].

    In case you might have any advice, here are the Criteria for filtering the "Dates" row in each subquery for qryBillableHours, qryExpenses and qryPayments: Between [Forms]![frmCreateInvoice]![txtInvoiceFromDate] And [Forms]![frmCreateInvoice]![txtInvoiceToDate].

    @KenSheridan, I don't think that's the problem, because when I run the queries manually everything in the Subreports comes out fine. I think the problem has something to do with the subreports being inside main report. My form cboMatterID box is opening the Invoice Report that doesn't have any information in it -- except a text box in the header that includes the MatterID (which I was thinking would be my Master-Child link to the Subreports!!). The main Invoice Report pulls the correct MatterID, but the subreports are not showing the Billables, Expenses and Payments that are linked to that MatterID. 

    I tried creating a comprehensive query that includes all information from my tblMatters, tblBillables, tblExpenses, tblPayments, so that I could base the Invoice Report on one, simple query. The issue I ran into was "ambiguous outer joins" due to the ClientID field being in each of the tables (I also tried to create subqueries but couldn't get the syntax right). So that's how I ended up using separate queries for each Subreports. 

    Thanks for the help you guys! 




    • Edited by le_kai Tuesday, March 7, 2017 5:21 AM
    Tuesday, March 7, 2017 5:19 AM
  • Let's be sure we have the full picture:

    1.  The parent report is bound and includes a control in its header bound to the MatterID column in the report's recordset.  The report is opened filtered to a specific MatterID value by means of the WhereCondition argument of the OpenReport method of the DoCmd object.

    2.  Each subreport control's LinkMasterFields and LinkChildFields properties are MatterID.  Whether there is a control in the parent report bound to MatterID is immaterial; all that matters is that MatterID is a column in the report's recordset.  It would be unusual to bind a control to a surrogate key column, as its value is generally arbitrary.  To show the matter in a report a text box control would be bound to the text Matter column.

    3.  Each subreport's query references controls in the calling form as parameters which define a date range.

    4.  The calling form remains open while the report is open.

    On that basis I see no reason why the report/subreports should not return the results which you expect.  I would still advise you to declare the parameters, however, as a matter of best practice.  Even though they are referenced correctly in one context, I would not assume that they will necessarily be referenced correctly in another context.  The only context in which a parameter must not be declared is where it is being evaluated for NULL to make the parameter optional, as a parameter declared as TEXT for instance will never evaluate to NULL.  A Parameter declared as DATETIME, however, is unusual in this respect and can evaluate to NULL, so should always be declared.

    Ken Sheridan, Stafford, England

    Tuesday, March 7, 2017 11:59 AM
  • @KenSheridan, yep, you have it exactly correct. That is my situation, but I figured out how to make the subreports show up -- I changed the subreport's FilterOnLoad property to 'Yes' and all reports are opening just fine. As for your suggestion about declaring my parameters in VBA, I'm just curious how that would be written? Would I simply copy and paste what I have in the underlying query into my OpenReport procedure as something like: 

    Dim InvoiceDates As DateTime ( Between [Forms]![frmCreateInvoice]![txtInvoiceFromDate] And [Forms]![frmCreateInvoice]![txtInvoiceToDate] )

    Also, in your previous post you offered two possible WhereCondition expressions for filtering the report. I am indeed using the latter -- the combobox dropdown displays the MatterName, but the BoundColumn is the MatterID. However, I still have trouble understanding why/when/where to use " , ' , and & in my expressions. In your first suggestion there are three sets of double quotes, for example, and I'm not exactly sure why those are necessary. Do the two extra sets of double quotes represent some zero-length string? 

    Tuesday, March 7, 2017 12:56 PM
  • To declare parameters you can either do so via the parameters dialogue in query design view, or by adding a PARAMETERS clause to the start of the query in SQL view:

    PARAMETERS [Forms]![frmCreateInvoice]![txtInvoiceFromDate] DATETIME,
    [Forms]![frmCreateInvoice]![txtInvoiceToDate] DATE TIME;
    SELECT etc.

    As regards the expressions for the WhereCondition argument, if the data type of the column in question is text then the value needs to be wrapped in literal quotes characters.  Because the quotes character is the delimiter for a text expression, you cannot simply put a quotes character within the expression, but a pair of contiguous quotes characters within an expression is interpreted as a literal quotes character, e.g.

    "SomeColumn = """ & somevariable & """"

    You could also use the single quote character:

    "SomeColumn = '" & somevariable & "'"

    but this would fail with values which include an apostrophe, like my own name in its original non-anglicized form, Cináed O'Siridean.

    If the data type is a number, as in your case, the value does not need to be wrapped in literal quotes characters, e.g.

    "SomeColumn = " & somevariable

    With date/time values, the delimiter for a date literal is the # character and the value has to be in US short date format or an otherwise internationally unambiguous format such as the ISO standard for date/time notation of YYYY-MM-DD hh:mm:ss (the standard uses case to distinguish between months and minutes, whereas Access uses nn for the latter), e.g.

    "SomeDateTimeColumn = #" & Format(somedatetimevariable, "yyyy-mm-dd hh:nn:ss") & "#"



    Ken Sheridan, Stafford, England

    • Marked as answer by le_kai Wednesday, March 15, 2017 4:08 AM
    Tuesday, March 7, 2017 4:19 PM
  • Hi,

    Thanks for clarifying about the parameter prompt, and I'm glad to hear you got the problem sorted out by setting the FilterOnLoad property to Yes.

    Cheers!

    Tuesday, March 7, 2017 4:26 PM