UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Not Stripping The "and" At The End Of The String, Access 2016    
 
   
alorenzini
post Jun 29 2020, 03:49 PM
Post#1



Posts: 838
Joined: 21-September 07
From: South Dakota


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.

CODE
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

[*** EDITED BY cheekybuddha to add code tags ***]

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
cheekybuddha
post Jun 29 2020, 04:25 PM
Post#2


UtterAccess Moderator
Posts: 13,010
Joined: 6-December 03
From: Telegraph Hill


Hi Art,

Is this the right code?

You don't have a filter for [RELTYPE] in there!

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Jun 29 2020, 04:34 PM
Post#3


UtterAccess VIP
Posts: 2,396
Joined: 4-June 18
From: Somerset, UK


The code also makes no reference to date filters despite the use of conJetDate as a constant. Is the code complete?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
alorenzini
post Jun 29 2020, 04:58 PM
Post#4



Posts: 838
Joined: 21-September 07
From: South Dakota


Sorry it should read as:

CODE
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.cboRelationshiptType) Then
                   strWhere = vWhere & "([RELTYPE] Like ""*" & Me.cboRelationshiptType & "*"") AND "
        End If
  
    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


I didn't add my date code because I was trying to keep it simple and direct.

[*** EDITED BY cheekybuddha to add code tags ***]

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
cheekybuddha
post Jun 29 2020, 05:16 PM
Post#5


UtterAccess Moderator
Posts: 13,010
Joined: 6-December 03
From: Telegraph Hill


Art,

Please take a second to add code tags when you post large tracts of code. (I added them again for you.)

It makes it much easier to read for those trying to assist you.

There doesn't appear to be anything obvious why the code shouldn't be working as you would expect it to. Have you tried stepping through it?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
June7
post Jun 29 2020, 06:30 PM
Post#6



Posts: 1,519
Joined: 25-January 16
From: The Great Land


Should
strWhere = vWhere & "([RELTYPE]

be
strWhere = strWhere & "([RELTYPE]

?

Although don't see how that would be an issue since it is in the first criteria element.

Do you have Option Explicit in module header?


This post has been edited by June7: Jun 29 2020, 06:32 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
penfold098
post Jun 30 2020, 07:46 AM
Post#7



Posts: 155
Joined: 5-March 14



If I may,, i noticed two small things that I am not sure will make a difference.

In your final iif, why do you check if the (string length - 5) is less than zero? If I followed your code correctly, strWhere will be zero-length if no options are selected, but it will not be " AND ". Would it be better just to check if len(strWhere = 0)?

Also, it is usually a best practice to have the more common alternative as the first part of the if-then-else block.

CODE
If len(strWhere >0) then
    strWhere = left(strWhere, len(strWhere)-5)
    ...
else
    msgbox("No criteria")
end if


HTH

Best of luck on your project.
This post has been edited by penfold098: Jun 30 2020, 07:47 AM
Go to the top of the page
 
projecttoday
post Jun 30 2020, 07:53 AM
Post#8


UtterAccess VIP
Posts: 12,380
Joined: 10-February 04
From: South Charleston, WV


If you're still having trouble with this I suggest doing:

CODE
Debug.Print strWhere
Stop
strWhere = Left$(strWhere, lngLen)

--------------------
Robert Crouser
Go to the top of the page
 
boobigbam@gmail....
post Jun 30 2020, 01:48 PM
Post#9



Posts: 13
Joined: 24-June 20



Perhaps you could check the strWhere in each IF so that each IF doesnt have to know if any other IF's later in the code will do anything

Somethink like this:

CODE
If Not IsNull(Me.txtFilterProjectNumber) Then
IF len(strWhere) = 0 then
  'build strWhere and dont add any AND in the end
else
  'theres is already somethin in strWhere so add "AND"
  strWhere = strWhere & " AND "
  strWhere = strWhere &  "next part of string"
End IF
End If

This post has been edited by boobigbam@gmail.com: Jun 30 2020, 01:56 PM
Go to the top of the page
 
alorenzini
post Jun 30 2020, 01:57 PM
Post#10



Posts: 838
Joined: 21-September 07
From: South Dakota


Ok. I did get it working. It was a the record source. 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.

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
projecttoday
post Jun 30 2020, 04:05 PM
Post#11


UtterAccess VIP
Posts: 12,380
Joined: 10-February 04
From: South Charleston, WV


Great. I new it had to be in strWhere not strWhere = Left$(strWhere, lngLen) since I checked that.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 09:47 AM