View Poll Results: Did you find this helpful for your project?

Voters
2. You may not vote on this poll
  • Yes

    1 50.00%
  • No

    1 50.00%
Results 1 to 8 of 8
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Invoice Form filtering Expenses

    Decided to add an Invoice Form. This form saves invoices with unique number, which is associated with a client. This form should filter expenses based on a date range between BeginDate and EndDate.



    There is an Expense Form. This form takes individual expenses and saves with unique number. This form is placed on the Invoice Form and should filter when a filter button is clicked after entering the BeginDate and EndDate located on the Invoice Form.

    Tried many variables but unlike other forms with subform relationships an error occurs when attempting to filter or all expenses are displayed without filtering.

    The question is whether anyone else has solved this issue and if so then how?

    It must be a form and not a report because invoice must have unique number, be saved, queried, and calculated later for additional invoices.

    Example of date filter with button that executes query of subform:

    Click image for larger version. 

Name:	Capture 1.PNG 
Views:	16 
Size:	2.5 KB 
ID:	32257

    Example for Expense Form that is placed as subform on Invoice Form:

    Click image for larger version. 

Name:	Capture 2.PNG 
Views:	16 
Size:	12.6 KB 
ID:	32258

    Example Error message (one of many different messages depending on methods tried):

    Click image for larger version. 

Name:	Capture 3.PNG 
Views:	16 
Size:	7.0 KB 
ID:	32259

    Other error messages has issue with the WHERE clause when trying to append the query because it does not recognize the #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# for a query, which is expected:

    Example VBA on main form when filter button clicked:
    Code:
    Private Sub btnFilterExpense_Click()
        Dim strSQL As String
        Dim strFilter As String
        Dim strFiltered As String
    
    
        strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
        CurrentDb.OpenRecordset strSQL
    
    
        strFilter = "WHERE [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# "
    
    
        strFiltered = strSQL & strFilter
    
    
        Me.subfrmQueryCaseExpense.SourceObject = strFiltered
    
    
        Me.subfrmQueryCaseExpense.Requery
    End Sub
    The following produces results in subform without error but also without filtering dates:
    Code:
    Private Sub btnFilterExpense_Click()
        Dim strSQL As String
        
        strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
        
        CurrentDb.OpenRecordset strSQL
        
        CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strSQL
        
        Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    End Sub
    The following produces error described earlier:
    Code:
    Private Sub btnFilterExpense_Click()
        Dim strSQL As String
        Dim strFilter As String
        Dim strFiltered As String
    
    
        strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
    
    
        CurrentDb.OpenRecordset strSQL
    
    
        strFilter = "WHERE [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# "
    
    
        strFiltered = strSQL & strFilter
    
    
        CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strFiltered
    
    
        Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    End Sub
    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	11.0 KB 
ID:	32263
    Last edited by SierraJuliet; 01-20-2018 at 01:32 PM.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The query order should be
    SELECT
    FROM
    WHERE
    ORDER BY

    so remove ORDER BY from the SELECT clause and put it after the WHERE clause

    The WHERE clause references a form control txtCaseNum so it needs delimiters - I've assumed it is a number as the name suggests!

    I also think you have a few superfluous lines of code.
    For example, you create a recordset but do nothing with it
    You create a query def but that's not needed unless you use the query elsewhere

    This is how I would do it.
    Hopefully it will run without error:


    Code:
    Private Sub btnFilterExpense_Click()
    Dim strSelect As String, strWhere As String, strOrderBy As String, strSQL As String
    
    strSelect = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense"
    
    strOrderBy = " ORDER BY [ExpenseDate]"
    
    strWhere = " WHERE [CaseNum] = " & Me.txtCaseNum & " And [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "#"
    
    strSQL = strSelect & strWhere & strOrderBy
    
    'I think the next 3 lines aren't needed
    'CurrentDb.OpenRecordset strSQL 
    'CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strSQL
    'Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    
    Me.subfrmQueryCaseExpense.RecordSource = strSQL
    
    End Sub
    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The ORDER BY was probably a typo because I switched back and forth trying many things. The subform cannot have a record source. It should be source object but even then returns and error unless set to a query, which the line before sets the query to before calling it in the subform.

    With this code I get column headers in subform but no data, so I am getting closer:

    Code:
    Private Sub btnFilterExpense_Click()
    	Dim strSQL As String
    	Dim strFilter As String
    	Dim strFiltered As String
    
    
    	strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense "
    
    
    	strFilter = "WHERE [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# ORDER BY [ExpenseDate] "
    
    
    	strFiltered = strSQL & strFilter
    
    
    	'set query SQL
    	CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strFiltered
    
    
    	'calls query for subform
    	Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    
    
    	'requery subform
    	Me.subfrmQueryCaseExpense.Requery
    End Sub
    This can all be condensed to:

    Code:
    Private Sub btnFilterExpense_Click()
    	Dim strSQL As String
    
    
    	strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense WHERE [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# ORDER BY [ExpenseDate] "
    
    
    	CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strSQL
    
    
    	Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    
    
    	Me.subfrmQueryCaseExpense.Requery
    End Sub
    Last edited by SierraJuliet; 01-20-2018 at 01:34 PM.

  4. #4
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Just verified by going into that query and removing the [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' then it filtered by date. Now I need to sort out why [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' is causing an error.
    Last edited by SierraJuliet; 01-20-2018 at 01:34 PM.

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Okay, just verified it is something with [CaseNum] = 'txtCaseNum' and also checked by specifying the form with [CaseNum] = [Forms]![frmViewMain]![frmInvoice]![txtCaseNum] and [CaseNum] Like [Forms]![frmViewMain]![frmInvoice]![txtCaseNum] to no avail.
    Last edited by SierraJuliet; 01-20-2018 at 01:34 PM.

  6. #6
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Okay folks, I got it to work. The following is the complete code.
    Code:
    Private Sub btnFilterExpense_Click()
        Dim strSQL As String
    
    
        strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense WHERE (([CaseNum] Like [Forms]![frmViewMain]![frmInvoice]![txtCaseNum]) And ([ExpenseStatus] = 'Not Paid') And ([ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "#)) ORDER BY [ExpenseDate] "
    
    
        CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strSQL
    
    
        Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
    
    
        Me.subfrmQueryCaseExpense.Requery
    End Sub
    For those interested. So what happens is on the frmInvoice, which is bound to the tblInvoice. There are two date boxes [Start Date] and [End Date] that the user enters desired dates. These dates are used in the strSQL above, which sets the SQL statement of qryInvoiceExpense when the user clicks the btnFilterExpense. The subfrmQueryCaseExpense is a subform that is placed on the frmInvoice. The subfrmQueryCaseExpense control source is set to the qryInvoiceExpense by the btnFilterExpense procedure, which updates the subfrmQueryCaseExpense. In addition, I wanted to sort the expenses first by applicable [CaseNum] followed by [ExpenseStatus] before filtering the date range because it would reduce the processing of records; thus, reducing load on the database and network.
    Last edited by SierraJuliet; 01-20-2018 at 01:36 PM.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad you got it working

    A request for future posts...
    Use code tags (# button) for all code you upload so its automatically separated & formatted correctly
    Then you won't need to do all the bold text / red text for the non-code sections ....!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I am new to this site. Thanks for the feedback, it will come in handy. Changes made above!
    Last edited by SierraJuliet; 01-20-2018 at 01:35 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-01-2016, 09:19 AM
  2. Replies: 2
    Last Post: 09-14-2015, 09:01 AM
  3. Replies: 1
    Last Post: 12-13-2013, 07:11 AM
  4. Replies: 3
    Last Post: 11-19-2012, 05:05 PM
  5. Replies: 1
    Last Post: 10-10-2012, 01:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums