none
DoCmd.ApplyFilter application RRS feed

  • Question

  • Is it possible to use DoCmd.ApplyFilter with two variable like this

    ******************************************************************************************************

    DoCmd.ApplyFilter , strAdvisor & strCriteria


    Tuesday, January 9, 2018 12:54 AM

Answers

  • Hi SpeedKing1000,

    below is the code posted by you.

        strInterest = "([Summer Transition/SASP Program of Interest] = '" & Me.Search_by_Program_of_Interest & "')"
             strSemester = "([Semester Recruited] = '" & Me.Search_by_Semester & "')"
             DoCmd.ApplyFilter , (strSemester & " AND " & strSemester)


    first I try to print the variable values in immediate window.

    Output:

    I can see there are round brackets.

    if you see your code, you will notice that in that line you had also use round brackets.

     

    I suggest you to remove all round brackets and again try to make a test.

    I try to make a test on my side.

    below is the data in table.

    code:

    Private Sub Form_Load()
    DoCmd.ApplyFilter , "AssemblyItem = 'abc' and ComponentItem='A'"
    End Sub
    

    Output:

    you can see both conditions are getting cheeked and it is working correctly.

    if I change the value in condition to verify that both conditions getting checked.

    code:

    Private Sub Form_Load()
    DoCmd.ApplyFilter , "AssemblyItem = 'abc' and ComponentItem='B'"
    End Sub

    Output:

    so you can see that both conditions get checked and it is working properly.

    I suggest you to remove unnecessary round brackets from your code and again make a test.

    let us know about your testing result.

    if issue persist then we will try to provide further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SpeedKing1000 Friday, March 23, 2018 9:35 AM
    Wednesday, January 10, 2018 2:04 AM
    Moderator

All replies

  • I think so... as long as the combination of the two variables resolves to a valid filter value. If not, you may have to insert a logical operator between them. For example:

    DoCmd.ApplyFilter , strAdvisor & " AND " & strCriteria

    Hope it helps...

    Tuesday, January 9, 2018 4:02 AM
  • Thank you I will give it a try now.
    Tuesday, January 9, 2018 9:27 PM
  • You're welcome. Good luck and let us know how it goes.
    Tuesday, January 9, 2018 10:00 PM
  • It didn't work it only filtered the first one. Do you thing there might be another way to try it?
    Tuesday, January 9, 2018 10:14 PM
  • Hi,

    Can you show us exactly what you tried and what were in those variables? Thanks.

    Tuesday, January 9, 2018 10:28 PM
  • So this is the code 

    Private Sub Search_Click()

        Dim strSemester As String, strSeTask As String
        Dim strSemesterstrSemester As String, strInTask As String
        
        If ((IsNull(Me.Search_by_Semester)) Or (IsNull(Me.Search_by_Program_of_Interest))) Then
            MsgBox "Please enter the Semester Recruited and Program of Interest", vbInformation, "Semester and Interest Required"
        Else
            strInterest = "([Summer Transition/SASP Program of Interest] = '" & Me.Search_by_Program_of_Interest & "')"
            strSemester = "([Semester Recruited] = '" & Me.Search_by_Semester & "')"
            DoCmd.ApplyFilter , (strSemester & " AND " & strSemester)
            
        End If
        

    End Sub

    Tuesday, January 9, 2018 10:30 PM
  • Okay, thanks. The above code tells me you are looking for students enrolled in a particular program within a specific semester. What did you enter or select in both Search_by_Program_of_Interest and Search_by_Semester? Are you sure the table has any records matching both conditions? If you want to return students in either search, then you could try using the OR operator in place of AND.

    Hope it helps...

    Tuesday, January 9, 2018 10:38 PM
  • Dim strAdvisor as String

    strAdvisor= A FIELD NAME ON YOUR FORM (Me.FIELDNAME for example)

    Dim strCriteria as String

    strCriteria= A FIELD NAME ON YOUR FORM

    DoCmd.ApplyFilter , "[FIELD NAME IN YOUR RECORD SOURCE]=" & strAdvisor & " And [FIELD NAME IN YOUR RECORD SOURCE] ='" & strCiteria & "'

    Tuesday, January 9, 2018 10:44 PM
  • Hi Speed,

    I just noticed your code used both strSemester rather than one strSemester and the other as strInterest.

    So, rather than:

    DoCmdApplyFilter , (strSemester & " AND " & strSemester)

    try:

    DoCmdApplyFilter , (strInterest & " AND " & strSemester)

    Cheers!

    Tuesday, January 9, 2018 10:48 PM
  • Yes the quarry have the record. In Search_by_Semester I entered Fall 2014 and in Search_by_Program_of_Interest I entered SASP but when I entered SASP only to test the each separately Search_by_Program_of_Interest did not yield any result. 

    Here is the information I am working with this code is contained inside the search button when clicked 

    Tuesday, January 9, 2018 10:49 PM
  • Hi SpeedKing,

    Did you see my last post? Was it just a typo then? Do you actually have strInterest and strSemester in your form's code?

    Tuesday, January 9, 2018 10:50 PM
  • Oh, by the way...

    If your criteria uses the equal sign, then the user entry must exactly match the record for it to show. If you want partial matches, then try using the Like operator. For example:

    "[FieldName] Like '*" & Me.TextboxName & "*'"

    Hope it helps...

    Tuesday, January 9, 2018 10:53 PM
  • I fix that error but still didn't make any difference 
    Tuesday, January 9, 2018 10:56 PM
  • I don't get what you mean by this
    Tuesday, January 9, 2018 10:57 PM
  • Hi SpeedKing1000,

    below is the code posted by you.

        strInterest = "([Summer Transition/SASP Program of Interest] = '" & Me.Search_by_Program_of_Interest & "')"
             strSemester = "([Semester Recruited] = '" & Me.Search_by_Semester & "')"
             DoCmd.ApplyFilter , (strSemester & " AND " & strSemester)


    first I try to print the variable values in immediate window.

    Output:

    I can see there are round brackets.

    if you see your code, you will notice that in that line you had also use round brackets.

     

    I suggest you to remove all round brackets and again try to make a test.

    I try to make a test on my side.

    below is the data in table.

    code:

    Private Sub Form_Load()
    DoCmd.ApplyFilter , "AssemblyItem = 'abc' and ComponentItem='A'"
    End Sub
    

    Output:

    you can see both conditions are getting cheeked and it is working correctly.

    if I change the value in condition to verify that both conditions getting checked.

    code:

    Private Sub Form_Load()
    DoCmd.ApplyFilter , "AssemblyItem = 'abc' and ComponentItem='B'"
    End Sub

    Output:

    so you can see that both conditions get checked and it is working properly.

    I suggest you to remove unnecessary round brackets from your code and again make a test.

    let us know about your testing result.

    if issue persist then we will try to provide further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SpeedKing1000 Friday, March 23, 2018 9:35 AM
    Wednesday, January 10, 2018 2:04 AM
    Moderator
  • I don't get what you mean by this

    Hi,

    I'll explain what I mean in a minute, but try entering the following first in the search boxes.

    For the Semester box, enter:

    Fall 2015

    For the Program of Interest, enter:

    BMI, FYE, SASP, Summer Bridge

    In other words, enter the exact values stored in the table and let us know if it works or not.

    I'll wait for your resopnse...

    Wednesday, January 10, 2018 2:36 AM
  • Hi SpeedKing1000,

    Is your issue resolved?

    You did not done any follow up on this thread for a long time.

    If your issue is solved then I suggest you to share your solution and mark it as an answer.

    If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

    If then after you have any further questions then you can let us know about it.

    We will try to provide further suggestions to solve it.

    I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

    So take appropriate steps to close this thread , If your issue is resolved.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 9:31 AM
    Moderator