Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: Passing a Parameter AND a Filter To A Query Based Report using one UI

    Hi There!
    I am putting together a simple database in Access 2007-2010.

    I have been successful on two parts, but am having difficulty putting them together!

    I have a query that runs based on an inputted value. In this case, it is calculating a monthly Depreciation Journal Entry based on the date of the Journal Entry. So I used the field "EntryDate: [Please Enter the Journal Entry Date]" in the query. The sum of records changes depending on this date (ie, was the asset bought or sold at said date). It all works, and I have created a beautiful report from it.

    It all works lovely.

    But there are a few different companies, and I would like to filter for a specific company. I was able to set up modal form that requested input based on a drop down box, and added the procedure to the button to open the query based report with the where condition "[company] = " + Me.CompanyName.

    And this works great too!

    But it's so tacky having two popups... I have been struggling to have the modal form pass both the filter AND the parameter to the query before the report opens. The filter is easy, but I cannot figure out how to pass the parameter. Maybe some code to add that passes the date to the "enter parameter value" box? Or do I have to use DAO and create a query based recordset, put the rst into a temp table, and create the report from there.

    That seems like so much just to pass on small parameter!!!

    I've searched around the forum, and I haven't found the answer yet. Can someone give me a suggestion?

    Thanks so much for any help!
    Naomi >^,,^<

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by nomij View Post
    The filter is easy, but I cannot figure out how to pass the parameter. Maybe some code to add that passes the date to the "enter parameter value" box? Or do I have to use DAO and create a query based recordset, put the rst into a temp table, and create the report from there.

    That seems like so much just to pass on small parameter!!!
    You must instanciate the Query in a DAO.Querydef object. There is no other way to pass values to a query with parameters, except the prompt boxes ("Please enter a value ...").

    And, by the way, is't not that much to do, less than 10 lines of VBA code.
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    6
    Thank you Sinndho for such a succinct answer!

    I know 10 lines of code is not a lot for someone who is fluent in VBA, but I'm learning as I go, so each line has to be researched and run, and debugged, and then looked up to see where I got the syntax wrong...

    But at least now I am not running around trying to figure out how to get the form to capture and pass the parameter!

    So, am I on the right track by doing this;

    Code:
        
    Set QdfDepEntryQry = db.QueryDefs("Monthly Depreciation Entry")
    QdfDepEntryQry.Parameters("[Please Enter the Journal Entry Date]") = Me.EntryDate
    Set RstDepEntry = QdfDepEntryQry.OpenRecordset
    (I know I haven't done the filter yet... I gotta get one thing working at a time! LOL!)

    So, if this gives me the recordset, can I create the report from the recordset, or should I populate a temp table, and create the report from that?

    Thanks so much if you are able help a bit more!!!

    Naomi

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Any DAO.Database object maintains a collection named QueryDefs, which is populated with DAO.QueryDef objects. Each QueryDef object in the QueryDefs collection represents (or is associated with) an existing query in an Access database. In an open database, this Database object can be referenced using the CurrentDb shorcut.

    If you want to manipulate an existing query in a VBA procedure, you instanciate a DAO.QueryDef object and itinialize it from and existing Querydef object existing in the QueryDefs collection of the database. Once you have initialized the QueryDef object, you have access to its properties and methods. When youre done using them, you clean up by closing que QueryDef object and set it to Nothing to remove it from the memory.

    In the following example, there is a query in the database named Qry_Placement which contains this SQL expression:
    Code:
    SELECT Tbl_Placements.SysCounter, Tbl_Placements.ServiceStartDate, Tbl_Placements.ServiceEndDate
    FROM Tbl_Placements INNER JOIN Tbl_Calendar ON Tbl_Placements.ServiceStartDate=Tbl_Calendar.Cal_Date
    WHERE (((Tbl_Placements.ServiceStartDate) Between #1/1/2012# And #2/1/2012#));
    I can access the SQL expression of the query (here to simply display this expression in the Immediate window) using the following code (7 lines of code):
    Code:
    Sub ManipulateExistingQuery()
    
        Dim qdf As DAO.QueryDef
        
        Set qdf = CurrentDb.QueryDefs("Qry_Placements")
        Debug.Print qdf.SQL
        qdf.Close
        Set qdf = Nothing
        
    End Sub
    The SQL property of a QueryDef object is a read/write property. This means that I can also change it:
    Code:
    Sub ManipulateExistingQuery()
    
        Dim qdf As DAO.QueryDef
        
        Set qdf = CurrentDb.QueryDefs("Qry_Placements")
        qdf.SQL = Replace(qdf.SQL, "2012", "2013")
        qdf.Close
        Set qdf = Nothing
        
    End Sub
    If I open the query Qry_Placement in SQL view after the execution of the procedure, it now contains the SQL expression:
    Code:
    SELECT Tbl_Placements.SysCounter, Tbl_Placements.ServiceStartDate, Tbl_Placements.ServiceEndDate
    FROM Tbl_Placements INNER JOIN Tbl_Calendar ON Tbl_Placements.ServiceStartDate=Tbl_Calendar.Cal_Date
    WHERE (((Tbl_Placements.ServiceStartDate) Between #1/1/2013# And #2/1/2013#));
    You could use a similar process to modify the criteria of the query (the values for EntryDate and for company in the WHERE part of the query).
    Have a nice day!

  5. #5
    Join Date
    Jan 2013
    Posts
    6
    Oh my goodness, that is way easier than my attempt!

    I put the query into a record set, then looped through updating each of the fields in an existing temp table, which just looked like it wasn't the neatest way to get the job done. Then I changed the data source of the report.

    I hadn't got it working perfectly when I thought to check back and I not only read your descriptive solution, but I learned another useful tool that I know I will be able to use again in another problem~! Thank you!

    But I haven't got it to work yet, and I'm wondering what I am missing.

    Code:
    Private Sub Command1_Click()
        Dim db As DAO.Database
        Set db = CurrentDb
         
        Dim qdf As DAO.QueryDef
        
        Set qdf = CurrentDb.QueryDefs("Monthly Depreciation Entry")
        qdf.SQL = Replace(qdf.SQL, "04/30/12", "05/31/12")
        qdf.Close
        Set qdf = Nothing
    End Sub
    When it runs it does not give any run-time errors. But the SQL doesn't change. Maybe my SQL stmt behind my Query is too long? I changed the field in my query to

    EntryDate: #4/30/2012#
    Instead of what it used to be:
    EntryDate:[Please Enter the Entry Date]

    :/ Any suggestions?

    Thanks so much again!
    Nomi >^,,^<

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You wrote that you changed the field in the query to: EntryDate: #4/30/2012#, while in the Replace() function that's supposed to transform its SQL property, you're looking for: 04/30/12

    The Replace() function works on Strings exclusively. No conversion to another data type is performed, so if the sought string is "04/30/12", "4/30/2012" dos not match, even if it's the same Date/Time value.
    Have a nice day!

  7. #7
    Join Date
    Jan 2013
    Posts
    6
    Hello Sinndho!
    Alas I thought "Ah Ha! I should have caught that, now all my problems are solved!" But the typo was not the cause of the problem :/

    The code does not change the SQL String. I'm not sure why. I fixed my code to from "04/30/12" to "4/30/12", but to no avail. And I also tried "#4/30/12#", and ran the code again, but again no luck. I'm not why it's not working. The SQL is quite long:


    Code:
    SELECT AssetList.ID, AssetList.AssetName, AssetList.SN, AssetList.PurchaseDate, AssetList.CapitalizedCost, AssetTypes.Catagory, AssetTypes.CCADepreciationRate, IIf([EntryDateMonth]<4,[EntryDateYear]-1,[EntryDateYear]) AS LastYearEnd, #4/30/2012# AS EntryDate, Month([EntryDate]) AS EntryDateMonth, Year([EntryDate]) AS EntryDateYear, [LastYearEnd]-[YearEndOfPurchase]+1 AS NumOfYearsDep, IIf(Month([PurchaseDate])<4,Year([PurchaseDate]),Year([PurchaseDate])+1) AS YearEndOfPurchase, [CapitalizedCost]*([CCADepreciationRate]/2) AS FirstYrDepn, IIf([NumOfYearsDep]>0,(([CapitalizedCost]-[FirstYrDepn])*((1-[CCADepreciationRate])^([NumOfYearsDep]-1))),0) AS BalanceAtLastYE, IIf([EntryDate]>[SoldDate],0,IIf([NumOfYearsDep]>0,[BalanceAtLastYE]*[CCADepreciationRate],IIf([EntryDate]<[PurchaseDate],0,IIf([NumOfYearsDep]=0,[CapitalizedCost]*([CCADepreciationRate]/2),0)))) AS DepnToBeRecordedCurrentYE, AssetList.SoldDate, IIf([EntryDate]>[SoldDate],"Sold","Not Sold") AS SoldAtEntryDate, IIf([NumOfYearsDep]>0,12,IIf(Month([PurchaseDate])>3,12-Month([PurchaseDate])+4,4-Month([PurchaseDate]))) AS MonthsToSpreadCurrentDepn, [DepnToBeRecordedCurrentYE]/[MonthsToSpreadCurrentDepn] AS DepnEntryForMonth, AssetList.Inactve, AssetList.Company, CompanyList.CompanyName
    FROM CompanyList INNER JOIN (AssetTypes INNER JOIN AssetList ON AssetTypes.ID = AssetList.AssetType) ON CompanyList.ID = AssetList.Company
    WHERE (((AssetList.Inactve)=False))
    ORDER BY AssetList.PurchaseDate;
    Thank you anyways for your help. I wonder what else I can try.
    Nomi >^,,^<

  8. #8
    Join Date
    Jan 2013
    Posts
    6
    But this:
    Code:
        MySQLString = "Blah Blah ,#4/30/12# Blah"
        MySQLString = Replace(MySQLString, "4/30/12", "5/31/12")
    works fine. So I'm stumped. Why would it work on the MySQLString, but not on the SQL String. Which I also put into the MySQLString variable and did the function on that, and it didn't work. I msgboxed it to make sure it was the right string, on both the qdf.SQL and MySQLString, and it is the SQL string of the query. I can see the 4/30/12 in the msgbox. I wonder why Access can't...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange. I used the following protocol to test the solution and everything seems to work:

    1. Call the procedure TestCreateQuery() to create a query with the SQL expression you provided, then open the new query in SQL View --> OK (see: ScreenShot_Test-Select Query_001.jpg).

    2. Call the procedure TestChangeQuery() to modify the SQL property of the query created at step 1 --> OK (see: ScreenShot_MsgBox-Test (Before).jpg & ScreenShot_MsgBox-Test (After).jpg).

    3. Re-open the query in SQL View to be sure that the changes were retained --> OK (see: ScreenShot_Test-Select Query_002.jpg).

    This was tested with Access 2003 sp3 and with Access 2010 sp1).

    Here are the two VBA procedures:
    Code:
    Sub TestCreateQuery()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        strSQL = "SELECT AssetList.ID, AssetList.AssetName, AssetList.SN, AssetList.PurchaseDate, AssetList.CapitalizedCost," & _
                 "AssetTypes.Catagory, AssetTypes.CCADepreciationRate, IIf([EntryDateMonth]<4,[EntryDateYear]-1,[EntryDateYear]) AS LastYearEnd, " & _
                 "#4/30/2012# AS EntryDate, Month([EntryDate]) AS EntryDateMonth, Year([EntryDate]) AS EntryDateYear, " & _
                 "[LastYearEnd]-[YearEndOfPurchase]+1 AS NumOfYearsDep, " & _
                 "IIf(Month([PurchaseDate])<4,Year([PurchaseDate]),Year([PurchaseDate])+1) AS YearEndOfPurchase, " & _
                 "[CapitalizedCost]*([CCADepreciationRate]/2) AS FirstYrDepn, " & _
                 "IIf([NumOfYearsDep]>0,(([CapitalizedCost]-[FirstYrDepn])*((1-[CCADepreciationRate])^([NumOfYearsDep]-1))),0) AS BalanceAtLastYE, " & _
                 "IIf([EntryDate]>[SoldDate],0,IIf([NumOfYearsDep]>0,[BalanceAtLastYE]*[CCADepreciationRate],IIf([EntryDate]<[PurchaseDate],0,IIf([NumOfYearsDep]=0,[CapitalizedCost]*([CCADepreciationRate]/2),0)))) AS DepnToBeRecordedCurrentYE, AssetList.SoldDate, IIf([EntryDate]>[SoldDate],'Sold','Not Sold') AS SoldAtEntryDate, " & _
                 "IIf([NumOfYearsDep]>0,12,IIf(Month([PurchaseDate])>3,12-Month([PurchaseDate])+4,4-Month([PurchaseDate]))) AS MonthsToSpreadCurrentDepn, " & _
                 "[DepnToBeRecordedCurrentYE]/[MonthsToSpreadCurrentDepn] AS DepnEntryForMonth, " & _
                 "AssetList.Inactve, AssetList.Company, CompanyList.CompanyName " & _
                 "FROM CompanyList INNER JOIN (AssetTypes INNER JOIN AssetList ON AssetTypes.ID = AssetList.AssetType) " & _
                 "ON CompanyList.ID = AssetList.Company " & _
                 "WHERE (((AssetList.Inactve) = False)) " & _
                 "ORDER BY AssetList.PurchaseDate;"
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("Test", strSQL)
        qdf.Close
        Set qdf = Nothing
        Set dbs = Nothing
        
    End Sub
    
    Sub TestChangeQuery()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("Test")
        With qdf
            MsgBox .SQL, vbInformation, "Test (Before)"
            .SQL = Replace(.SQL, "4/30/2012", "5/31/12")
            .Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
        MsgBox CurrentDb.QueryDefs("Test").SQL, vbInformation, "Test (After)"
            
    End Sub
    Attached Thumbnails Attached Thumbnails ScreenShot_Test-Select Query_001.jpg   ScreenShot_MsgBox-Test (Before).jpg   ScreenShot_MsgBox-Test (After).jpg   ScreenShot_Test-Select Query_002.jpg  
    Have a nice day!

  10. #10
    Join Date
    Jan 2013
    Posts
    6
    2012! BAH! I am so red in the face! You caught my Typo already, and I STILL missed it!

    I copied and pasted your code into my Access, and yup, worked no Prob!
    My "hover" isn't working in the code when I step through... it flickers away, so I added a watch to the SQL string. I took a screen shot to illustrate the mystery. I stepped through the replace line, and when I went take the second screen shot I saw it... 4/30/2012 not 4/30/12!!!!

    Your patience, your help, your everything, thank you! All that extra for a simple Typo!
    And that replace function on the SQL... I cannot imagine how many more times I will use it in the future!!!

    Thank you!
    Nomi >^,,^<

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're more than welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •