I was stepping through the code and it was not showing me my error. But I did get it working. It was a the record source of the report. I changed it from the name of a query to a true SELECT statement and it started working. Not sure why but OK. Thank you for everyone input.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
On Monday, June 29, 2020, 04:13:59 PM CDT, Duane Hookom <duanehookom@hotmail.com> wrote:
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
