Results 1 to 4 of 4
  1. #1
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    Applying filters on a form – calculated field

    Good morning Forum,

    I am using a code that I found on internet to apply filters on a form whose recourdsource is Table1.

    I have 10 unbound controls as filters. Nine of them work beautifully. The last one should apply a filter based on a calculated field and I don’t know how to make it work.

    Filter Control name: FltStatus (Combo with 3 choices : Expired, In progress or Future
    Calculated control: TxtStatus (Calculation based on dates, displays “Expired”, or “In progress” or “Future”)

    The code (the first filter works well and is a numeric field, from Table1):
    Code:
    Private Sub BtnFilter_Click()
    Dim strWhere As String                  
        Dim lngLen As Long                     
        Const conJetDate = "\#mm\/dd\/yyyy\#"   
    
               If Not IsNull(Me.FltLease) Then
                  strWhere = strWhere & "([Lease] = " & Me.FltLease & ") AND "
               End If
       
               If Not IsNull(Me.FltStatus) Then
                  strWhere = strWhere & "([TxtStatus] = """ & Me.FltStatus & """) AND "
               End If
    
               '.... other filters that work fine
    
    
    lngLen = Len(strWhere) - 5
        If lngLen <= 0 Then     
            MsgBox "No criteria", vbInformation, "Nothing to do."
        Else                    
            strWhere = Left$(strWhere, lngLen)
            Me.Filter = strWhere
            Me.FilterOn = True
        End If
    End Sub
    Of course, TxtStatus not originating from Table1, the filter doesn’t work.
    How to write the strWhere in this case?

    Thank you very much in advance and have a good day all.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to move your calculated control calculation into the form recordsource - i.e. use a query to include the calculation, rather than a table

  3. #3
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Hello Ajax,

    Thank you for your help. So I understand that there is no way to avoid the creation of a query in this case?

    I was hoping…

    Since I have no problem creating the query and incorporating the calculated field, I will mark this thread as Solved (Is there a Semi-Solved option ? )

    Cheers.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So I understand that there is no way to avoid the creation of a query in this case?
    it is better practice to use queries rather than just tables

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

Similar Threads

  1. Replies: 4
    Last Post: 02-09-2019, 05:38 PM
  2. Replies: 1
    Last Post: 04-30-2015, 08:33 PM
  3. Replies: 10
    Last Post: 04-23-2014, 04:49 PM
  4. Replies: 4
    Last Post: 04-22-2013, 06:45 AM
  5. Replies: 6
    Last Post: 10-16-2012, 07:10 AM

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