Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

 

Art-

You say you have three check boxes to indicate one, two, or three bedrooms, but your code references something called cboBedroomCount.

As for the community check boxes, are they all independent, or are they in an option group so that the user can select only one?

If independent, you need to do something like:

Dim strComFilter As String

   If Me.chkBearCreek Or Me.chkBlackFoot Or Me.chkBridger Then

       strComFilter = "("

       If Me.chkBearCreek Then

           strComFilter = strComFilter & "(CommunityName LIKE '*Bear Creek*') OR "

       End If

       If Me.chkBlackFoot Then

           strComFilter = strComFilter & "(CommunityName LIKE '*Black Foot*') OR "

       End If

       If Me.chkBridger Then

           strComFilter = strComFilter & "(CommunityName LIKE '*Bridger*')"

       End If

       strWhere = strWhere & strComFilter & ") AND "

    End If

If you can code an exact match on the name of the community, then that would be preferable to using LIKE.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On May 17, 2017, at 10:25 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a continuous form  called frmUnitList in the form header I have unbound fileds called txtTo txtFrom for a date range search, chkOne, chkTwo, chkThree for room count search and chkBearCreek, chkBlackFoot, txtBridger for town searches. I have the the following code for the base query of the form:

SELECT tblUnit.UnitID, tblUnit.UnitNumber, tblUnit.UnitCode, tblUnit.HUDFlag, tlkpCommunity.CommunityID, tlkpCommunity.CommunityName, tblUnit.BedroomCount, tblUnit.ClassificationType, tblUnit.HandicappedFlag, tblUnit.NAHASDAFlag, tblUnit.AmerIndFlag, tblUnit.Active
FROM tblUnit LEFT JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID;

The form also contains a command button called cmdFIlter, its code is as follows:

The continues form field called txtCommunity is a textbox and it associate search control is a checkbox (chkBearCreek, chkBlackFoot, txtBridger ).  So a user can select multiple community checkboxs to saearch for but how do I trtanslate that to the textbox?

Also how would I search the date range?

Private Sub cmdFilter_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.
    
    '**********************************************************************************
    '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.txtUnitNumber) Then
        strWhere = strWhere & "([UnitNumber] Like ""*" & Me.txtUnitNumber & "*"") AND "
    End If
    
    If Not IsNull(Me.txtCommunityName) Then
        strWhere = strWhere & "([CommunityName] Like ""*" & Me.txtCommunityName & "*"") AND "
    End If

        
    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboBedroomCount) Then
        strWhere = strWhere & "([RoomCountID] = " & Me.cboBedroomCount & ") AND "
    End If
    
    If Not IsNull(Me.cboSelectionType) Then
        strWhere = strWhere & "([SelectionTypeID] = " & Me.cboSelectionType & ") AND "
    End If
    
    'Classification Type
       'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.chkHandicapped = -1 Then
        strWhere = strWhere & "([HandicapUnitReqFlag] = True) AND "
    ElseIf Me.chkHandicapped = 0 Then
        strWhere = strWhere & "([HandicapUnitReqFlag] = False) AND "
    End If
    
       'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.chkHandicapped = -1 Then
        strWhere = strWhere & "([HandicapUnitReqFlag] = True) AND "
    ElseIf Me.chkHandicapped = 0 Then
        strWhere = strWhere & "([HandicapUnitReqFlag] = 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
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Thank you,'

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___