[MSAccessProfessionals] Not stripping the "AND"

I have the following code that I found and am trying to adapt. It geared to filter a form by allowing the user to select criteria from several combo boxes.

Private Sub cmdApplyFilter_Click()

     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.

    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _

                        we remove the trailing " AND " at the end.

    '           2. The date range works like this: _

                        Both dates      = only dates between (both inclusive. _

                        Start date only = all dates from this one onwards; _

                        End date only   = all dates up to (and including this one).

    

    Dim strWhere As String                  'The criteria string.

    Dim lngLen As Long                      'Length of the criteria string appended to

    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    Dim strDisplayString As String

    '**********************************************************************************

    'Look at each search box, and build up the criteria string from the non-blank ones.

    '**********************************************************************************

    

    'Text field example. Use quotes around the value in the string

  

    If Not IsNull(Me.txtFilterProjectNumber) Then

        strWhere = strWhere & "([Project Number] Like ""*" & Me.txtFilterProjectNumber & "*"") AND "

    End If

    

    If Not IsNull(Me.txtFilterRoomCount) Then

        strWhere = strWhere & "([BedroomCount] Like ""*" & Me.txtFilterRoomCount & "*"") AND "

    End If

    

    If Not IsNull(Me.txtFilterCommunityName) Then

        strWhere = strWhere & "([CommunityName] Like ""*" & Me.txtFilterCommunityName & "*"") AND "

    End If

    

    

    If Not IsNull(Me.txtFilterUnitCode) Then

        strWhere = strWhere & "([UnitCode] Like ""*" & Me.txtFilterUnitCode & "*"") AND "

    End If

    

    If Not IsNull(Me.txtFilterUnitNumber) Then

        strWhere = strWhere & "([UnitNumber] Like ""*" & Me.txtFilterUnitNumber & "*"") AND "

    End If

 

       'Number field example. Do not add the extra quotes.

    If Not IsNull(Me.cboFilterUnitStatus) Then

        'strWhere = strWhere & "([UnitStatus] = " & Me.cboFilterUnitStatus & ") AND "

        strWhere = strWhere & "([UnitStatus] Like ""*" & Me.cboFilterUnitStatus & "*"") AND "

    End If

    

    'Number field example. Do not add the extra quotes.

    If Not IsNull(Me.cboFilterHousingType) Then

        'strWhere = strWhere & "([UnitStatus] = " & Me.cboFilterUnitStatus & ") AND "

        strWhere = strWhere & "([UnitHousingType] Like ""*" & Me.cboFilterHousingType & "*"") AND "

    End If

    

       'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.

    If Me.chkFilterHandicapped = -1 Then

        strWhere = strWhere & "([HandicappedFlag] = True) AND "

    ElseIf Me.chkFilterHandicapped = 0 Then

        strWhere = strWhere & "([HandicappedFlag] = False) AND "

    End If

 

 '***********************************************************************

    'Chop off the trailing " AND ", and use the string as the form's Filter.

    '***********************************************************************

    'See if the string has more than 5 characters (a trailng " AND ") to remove.

    lngLen = Len(strWhere) - 5

    If lngLen <= 0 Then     'Nah: there was nothing in the string.

        MsgBox "No criteria", vbInformation, "Nothing to do."

    Else                    'Yep: there is something there, so remove the " AND " at the end.

        strWhere = Left$(strWhere, lngLen)

        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).

        Debug.Print strWhere

        Me.txtCriteria = strWhere

        'Finally, apply the string as the form's Filter.

        Me.Filter = strWhere

        Me.FilterOn = True

    End If

 

End Sub

The problem that I am running into is if its the last selection the code needs to knock off the last "AND" but it is not working. The string comes out looking like this:

([RELTYPE] Like "*HOH*") AND 

Any ideas?

Thank you 
Art Lorenzini
Sioux Falls, SD

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115660) | Reply To Group | Reply To Sender | Mute This Topic | New Topic

Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_