locked
MS Access overwrites parameter query value RRS feed

  • Question

  • I have a report in MS Access 2010 whose data is based on a parameter query.  To aid the end-user, the query's parameter value is also displayed along with the results.  However, if 0 records are returned, then MS Access overwrites the query's parameter value to NULL.

    Is this a documented and known behavior?

    I've asked this elsewhere, but I'm hoping I can get a definitive answer from this forum.

    Background Information:

    End-users run and printout reports all the time.  The parameter queries are used as filters.

    When looking at a printout, beyond the requested data, the report's header and footer displays important information such as:
    • Title of the report
    • Date and time report was run
    • Values of parameters used to filter the data

    You can see how this bug causes incorrect reports to be generated.

    Example:

    Lets say there's a table called tblMessages which has a field called ID.  This table only has 3 records in which the values of ID are: 1, 2, and 3.

    There's a query called qryMessages which retrieves all records whose ID is >= a requested minimum value.  But, if no minimum value is entered, then all records are returned.

    PARAMETERS [Minimum ID value] Long;
    SELECT *
    FROM tblMessages
    WHERE Iif( IsNull( [Minimum ID value] ), True, id >= [Minimum ID value] );

    And, of course, a report called rptMessages whose record source is qryMessages.  There's also a text box in the report header which displays the value of the parameter.  The control source for that text box is: =IIf(IsNull([Minimum ID value]),"Null",[Minimum ID value])

    Test Case 1:  the end-user enters nothing when prompted for the minimum ID value.  The report displays 3 records, and the report header contains the text "Minimum ID entered = NULL"

    Test Case 2:  the end-user enters 3 for the minimum ID value.  The report shows 1 record, and displays the text "Minimum ID entered = 3"

    Test Case 3:  the end-user enters 4 for the minimum ID value.  The report shows 0 records, but the header contains the text "Minimum ID entered = NULL"


    Friday, June 14, 2013 1:30 PM

Answers

  • I'm right with you on the dummy record.  I got a working example, but yes, I need to know how:

    • Not to display the dummy record
    • Calculate the correct number of records returned.  I didn't bring this up of course since this was just a simplified example to explain the issue.

    If you give the dummy record a distinct key value in some field, then you can put code in the Format event of the report's Detail section to cancel the event if that field has the "dummy" value.  For example,

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
        If Me.ID = -1 Then Cancel = True
        
    End Sub
    

    As for the number of records returned, you can put a text box in the Report Footer section with a controlsource expression like this:

        =Count(*)-1

    You have to subtract 1 from the actual count so as not to count the dummy record.

    Then, of course, I have this mocked up workaround union query laying around.  Which isn't ideal.  Some of my end-users are good at MS Access.  The file I produce is mainly used as the UI to a SQL Server DB; but it's also designed to be used as a template for their own custom queries and reports.  Their queries and reports utilize the queries I setup for them.

    You don't have to store the union query.  You can set the report's recordsource to the SQL statement that is the union query, without creating a separate stored query for it. For example, if your parameter query is qryMessages, returning fields ID, MsgDate, MsgText (for example), you might set the report's recordsource to something like this:

    SELECT -1 As ID, #12/30/1899# As MsgDate, "DUMMY" As MsgText FROM tblDummy 
    UNION ALL 
    SELECT ID, MsgDate, MsgText FROM qryMessages

    Since that query lives only in the report's RecordSource property, there's no stored query for your users to muck around with.

    Not a DAO query.  Hmmm.  OK, how does a report execute it's record source?  I'd love to step through some VBA code to see what's going on.  I only know of 3 types of queries within MS Access 2010:

    • Named Query - from the navigation pane
    • DAO
    • ADO

    I can't step through a named query, but I can do so for the DAO and ADO queries.

    Those are not types of queries, and you shouldn't think of them that way. A query is, at bottom, a SQL statement that instructs the database engine to perform an operation. A stored query, as shown in the navigation pane, is a representation of a DAO QueryDef object, which is a structure containing, among other things, the SQL statement that defines a query. "Opening" a stored query, whether by double-clicking or executing DoCmd.OpenQuery, tells the database engine to execute that SQL statement and, if it's the type of query that returns records, display those records in a datasheet.  There's a whole lot that goes on behind the scenes to make that query's *results* show up in a datasheet.

    DAO and ADO are libraries that define objects that may be used -- among other things -- to execute queries. Access (in an .accdb or .mdb) uses DAO to manage the database and run queries. Access also provides the OpenQuery and RunSQL methods, which place a sort of management layer over the DAO methods that it uses to interact with the database engine.  For example, when you use OpenQuery to execute a query, Access resolves the parameters for you.  If you use DAO in your code to execute a query, you have to provide the parameter values yourself.

     Got any recommendations for MS Access documentation at the level I'm looking at?  Any suggested reference books? 

    Actually reading the help file is good, if you're technicallty oriented and very patient.  I can also recommend the "Inside Out" series:  "Microsoft Access <version> Inside Out".  For a dated but still terrific book oriented toward developers, you could read one version or another of "Access <version> Developer's Handbook", though the last version was for Access 2002, and there's a lot of new stuff that came in with Access 2007.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Dummy yoyo Friday, June 21, 2013 10:13 AM
    Friday, June 14, 2013 7:47 PM

All replies

  • Good Morning Marshall,

    Let's start off by saying; it isn't a bug by definition as the result is to be expected with the way it is coded.

    What you need to do is before using a value as a parameter is test to see if the value is valid, and if it is then run your parameter, if not valid then prompt user for a valid parameter. You guaranty Null for any value that is False in the Where clause combined with the Texbox ControlSource.


    Chris Ward

    Friday, June 14, 2013 2:34 PM
  • Hey Chris,

    4 is a valid value.  And returning 0 records is the expected result.  What's messing us up is the fact that MS Access overwrites the end-user's entry of 4 with NULL.  The report should read "Minimum ID entered = 4" and display no records.

    Concerning NULL, the end-user can opt not to enter a value for [Minimum ID value]; thus setting its value to NULL.  In this case, the query's where clause becomes "WHERE True".  Obviously, not applying a filter and returning all records.

    The NULL within the textbox's controlSource outputs the text "Null" if [Minimum ID value] = NULL.  Otherwise, it outputs the value of [Minimum ID value].  This is all for readability.

    BTW, thanks for the quick response.

    Friday, June 14, 2013 2:59 PM
  • I have a report in MS Access 2010 whose data is based on a parameter query.  To aid the end-user, the query's parameter value is also displayed along with the results.  However, if 0 records are returned, then MS Access overwrites the query's parameter value to NULL.

    Is this a documented and known behavior?

    I've asked this elsewhere, but I'm hoping I can get a definitive answer from this forum.

    Background Information:

    End-users run and printout reports all the time.  The parameter queries are used as filters.

    When looking at a printout, beyond the requested data, the report's header and footer displays important information such as:
    • Title of the report
    • Date and time report was run
    • Values of parameters used to filter the data

    You can see how this bug causes incorrect reports to be generated.

    Example:

    Lets say there's a table called tblMessages which has a field called ID.  This table only has 3 records in which the values of ID are: 1, 2, and 3.

    There's a query called qryMessages which retrieves all records whose ID is >= a requested minimum value.  But, if no minimum value is entered, then all records are returned.

    PARAMETERS [Minimum ID value] Long;
    SELECT *
    FROM tblMessages
    WHERE Iif( IsNull( [Minimum ID value] ), True, id >= [Minimum ID value] );

    And, of course, a report called rptMessages whose record source is qryMessages.  There's also a text box in the report header which displays the value of the parameter.  The control source for that text box is: =IIf(IsNull([Minimum ID value]),"Null",[Minimum ID value])

    Test Case 1:  the end-user enters nothing when prompted for the minimum ID value.  The report displays 3 records, and the report header contains the text "Minimum ID entered = NULL"

    Test Case 2:  the end-user enters 3 for the minimum ID value.  The report shows 1 record, and displays the text "Minimum ID entered = 3"

    Test Case 3:  the end-user enters 4 for the minimum ID value.  The report shows 0 records, but the header contains the text "Minimum ID entered = NULL"

    This is not a bug, in that it is the correct result for the way queries and reports are processed, but that doesn't mean you aren't going to have to work around it.

    First, why it's not a bug:  On the report, where do you think the value for the parameter [Minimum ID value] come from?  It comes from the records returned by the query that is run as the report's recordsource.  Yes, that parameter isn't necessarily named in the report's RecordSource property, but Access actually constructs a separate, temporary query based on the report's RecordSource property and other properties of the report, and uses that as the true run-time recordsource query.  If you base your report on a parameter query, and you have a control that refers to one of those parameters by name, Access includes the parameter as a calculated field in the temporary query it generates.  However, if there are no records returned by the query, there are no records to provide the value of that calculated field, either.

    What you might do is change the report's recordsource so that it refers to a form control for the criterion value, instead of a prompted parameter. Note that it's still a parameter as far as the query engine is concerned, but it will be resolved from the form/control reference instead of prompting the user. Then, on the report, refer to the form control for the value of the parameter. Since the form will still be open, even if there are no records, you should be able to pick up the value from the form with no difficulty.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, June 14, 2013 3:18 PM
  • I think 1 way to overcome this problem is to use a "Pre-Processor" Form which has a TextBox [txtMinID] for the user to enter the value and a CommandButton to open the Report.

    When the user command your database to create the Report, open the "Pre-Processor" Form in lieu.  The user then enter the required MinID and then click the CommandButton to (actually) open the Report.

    For this to work, you need to change the RecordSource SQL to use the TextBox as the Parameter.  Personally, I would construct the WHERE clause something like:

    PARAMETERS [Forms]![frmPreProcessor]![txtMinID] Long;
    SELECT *
    FROM tblMessages
    WHERE ([id] >= [Forms]![frmPreProcessor]![txtMinID])
       OR ([Forms]![frmPreProcessor]![txtMinID] Is Null);

    Use the same Control reference above as the ControlSource for the TextBox on the ReportHeader of your Report and it should display the number that the user enters.  If the user enters nothing, you can display blank or the word "Null" in the TextBox on the Report by setting the Format Property of this TextBox.


    Van Dinh


    • Edited by Van Dinh Friday, June 14, 2013 3:40 PM
    Friday, June 14, 2013 3:38 PM
  • Hi Dirk,

    I understood your reasoning, however I disagree that this isn't a bug.  I'd love some official MS references concerning this behavior.  I've searched and searched.

    I'm still stating this is a bug because I can create a query within VBA that doesn't exhibit this behavior.  Ref:  Access 2010 Developer's Reference - Parameters Collection

        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
    
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("qryMessages")
    
        'Set the value of the QueryDef's parameter
        qdf.Parameters("Minimum ID value").Value = 4
    
        'Execute the query
        Set rst = qdf.OpenRecordset
        
        Debug.Print "Records returned = " + CStr(rst.RecordCount) ' Records returned = 0
        Debug.Print "Recordset Fields count = " + CStr(rst.Fields.Count) ' Recordset Fields count = 2
        Debug.Print "Minimum ID value = " + CStr(qdf.Parameters("Minimum ID value").Value) ' Minimum ID value = 4
    
        'Clean up
        qdf.Close
        Set qdf = Nothing
        Set dbs = Nothing

    If MS Access' reports create a temporary query from their record source; then this new temporary query still has the unaltered value entered by the end-user within it's parameter collection.

    Thank you for the suggestion of using a form.  I know I can use a workaround of a form; and TempVars seem to work too.  However, both of them require supporting code as well as some cons which I'd rather just leave off here.  Don't want to get too off topic.

    Parameter queries just work.  I can reuse them as is within forms, reports, and other queries with no supporting code or other requirements.  Well, all except for this issue here :(

    Thanks for being patient with my questions,

    Marshall

    Friday, June 14, 2013 4:42 PM
  • I'm still stating this is a bug because I can create a query within VBA that doesn't exhibit this behavior.  Ref:  Access 2010 Developer's Reference - Parameters Collection

        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
    
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("qryMessages")
    
        'Set the value of the QueryDef's parameter
        qdf.Parameters("Minimum ID value").Value = 4
    
        'Execute the query
        Set rst = qdf.OpenRecordset
        
        Debug.Print "Records returned = " + CStr(rst.RecordCount) ' Records returned = 0
        Debug.Print "Recordset Fields count = " + CStr(rst.Fields.Count) ' Recordset Fields count = 2
        Debug.Print "Minimum ID value = " + CStr(qdf.Parameters("Minimum ID value").Value) ' Minimum ID value = 4
    
        'Clean up
        qdf.Close
        Set qdf = Nothing
        Set dbs = Nothing

    If MS Access' reports create a temporary query from their record source; then this new temporary query still has the unaltered value entered by the end-user within it's parameter collection.

    But that's not the same thing at all!  Setting and then reading the Parameters collection of a QueryDef object is all well and good, but you do not have access to the QueryDef actually used by the report. You don't even (in an .accdb or .mdb) have access to the report's Recordset -- not that that would help you, because the recordset doesn't have a Parameters collection, nor does it contain any reference to the Parameters collection of the QueryDef from which it was opened (if in fact there was one).

    A DAO QueryDef is a different thing from an Access Report, and you shouldn't be surprised to discover that they have different properties.  I'm trying to find some way to get a report to show a query parameter even when there are no records returned by the query, but so far -- unsurprisingly -- I'm not having any luck.

    Another workaround would be to set up the report's recordsource so that it UNIONs a single dummy record from another source with the original parameter query.  Then, I think, since you would always have at least that one record, your control reference to the parameter would work.  I haven't tested that, so this is just a guess.  You would have to rig the report to suppress the actual printing of the dummy record.  That makes me think this workaround is not as good as using a form to supply the criterion.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, June 14, 2013 5:59 PM
  • Hey Dirk,

    I'm right with you on the dummy record.  I got a working example, but yes, I need to know how:

    • Not to display the dummy record
    • Calculate the correct number of records returned.  I didn't bring this up of course since this was just a simplified example to explain the issue.

    Then, of course, I have this mocked up workaround union query laying around.  Which isn't ideal.  Some of my end-users are good at MS Access.  The file I produce is mainly used as the UI to a SQL Server DB; but it's also designed to be used as a template for their own custom queries and reports.  Their queries and reports utilize the queries I setup for them.

    Not a DAO query.  Hmmm.  OK, how does a report execute it's record source?  I'd love to step through some VBA code to see what's going on.  I only know of 3 types of queries within MS Access 2010:

    • Named Query - from the navigation pane
    • DAO
    • ADO

    I can't step through a named query, but I can do so for the DAO and ADO queries.  BTW, I can run a named query via DoCmd.OpenQuery.  That function seems to execute a named query exactly like double-clicking on a named query; even using the "Enter Parameter Value" dialog boxes and verifying the values against the data type.

    But, alas, I can't step into OpenQuery.  Nor have I found any documentation stating how OpenQuery works; i.e. via DAO, ADO, or something else.

    Is everyone sick of me countering their answers yet?  If I haven't said it before, many thanks to everyone who is trying to help me out.  I very much appreciate this effort.

    Dirk, you are my new best friend for being so patient.  Got any recommendations for MS Access documentation at the level I'm looking at?  Any suggested reference books?  My M.O. is to try to answer my own questions first through research.  Only when I hit a wall do I branch out to these forums for help.

    If I miss anyone's reply, y'all have a great weekend,

    Marshall

    Friday, June 14, 2013 6:38 PM
  • I'm right with you on the dummy record.  I got a working example, but yes, I need to know how:

    • Not to display the dummy record
    • Calculate the correct number of records returned.  I didn't bring this up of course since this was just a simplified example to explain the issue.

    If you give the dummy record a distinct key value in some field, then you can put code in the Format event of the report's Detail section to cancel the event if that field has the "dummy" value.  For example,

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
        If Me.ID = -1 Then Cancel = True
        
    End Sub
    

    As for the number of records returned, you can put a text box in the Report Footer section with a controlsource expression like this:

        =Count(*)-1

    You have to subtract 1 from the actual count so as not to count the dummy record.

    Then, of course, I have this mocked up workaround union query laying around.  Which isn't ideal.  Some of my end-users are good at MS Access.  The file I produce is mainly used as the UI to a SQL Server DB; but it's also designed to be used as a template for their own custom queries and reports.  Their queries and reports utilize the queries I setup for them.

    You don't have to store the union query.  You can set the report's recordsource to the SQL statement that is the union query, without creating a separate stored query for it. For example, if your parameter query is qryMessages, returning fields ID, MsgDate, MsgText (for example), you might set the report's recordsource to something like this:

    SELECT -1 As ID, #12/30/1899# As MsgDate, "DUMMY" As MsgText FROM tblDummy 
    UNION ALL 
    SELECT ID, MsgDate, MsgText FROM qryMessages

    Since that query lives only in the report's RecordSource property, there's no stored query for your users to muck around with.

    Not a DAO query.  Hmmm.  OK, how does a report execute it's record source?  I'd love to step through some VBA code to see what's going on.  I only know of 3 types of queries within MS Access 2010:

    • Named Query - from the navigation pane
    • DAO
    • ADO

    I can't step through a named query, but I can do so for the DAO and ADO queries.

    Those are not types of queries, and you shouldn't think of them that way. A query is, at bottom, a SQL statement that instructs the database engine to perform an operation. A stored query, as shown in the navigation pane, is a representation of a DAO QueryDef object, which is a structure containing, among other things, the SQL statement that defines a query. "Opening" a stored query, whether by double-clicking or executing DoCmd.OpenQuery, tells the database engine to execute that SQL statement and, if it's the type of query that returns records, display those records in a datasheet.  There's a whole lot that goes on behind the scenes to make that query's *results* show up in a datasheet.

    DAO and ADO are libraries that define objects that may be used -- among other things -- to execute queries. Access (in an .accdb or .mdb) uses DAO to manage the database and run queries. Access also provides the OpenQuery and RunSQL methods, which place a sort of management layer over the DAO methods that it uses to interact with the database engine.  For example, when you use OpenQuery to execute a query, Access resolves the parameters for you.  If you use DAO in your code to execute a query, you have to provide the parameter values yourself.

     Got any recommendations for MS Access documentation at the level I'm looking at?  Any suggested reference books? 

    Actually reading the help file is good, if you're technicallty oriented and very patient.  I can also recommend the "Inside Out" series:  "Microsoft Access <version> Inside Out".  For a dated but still terrific book oriented toward developers, you could read one version or another of "Access <version> Developer's Handbook", though the last version was for Access 2002, and there's a lot of new stuff that came in with Access 2007.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Dummy yoyo Friday, June 21, 2013 10:13 AM
    Friday, June 14, 2013 7:47 PM