Hi Art,
Have you added a breakpoint to your code and stepped through it line by line?
As you press F8 you should be able to see what's happening so it can be fixed.
| The code will stop running and allow you to hover the mouse over a variable to see its value. You can press [F8] to step through the code a line at a time. www.tek-tips.com |
BW: RELTYPE is not even in your supplied code. I'm not sure where it is coming from.
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Art Lorenzini via groups.io <dbalorenzini=yahoo.com@groups.io>
Sent: Monday, June 29, 2020 3:34 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Not stripping the "AND"
Sent: Monday, June 29, 2020 3:34 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [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
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
Tidak ada komentar:
Posting Komentar