Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

 

Yes, that's the code.  I left out one important point when I send you that code.  Corrected below.

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

       ' Get rid of extraneous OR on the end

       If Right(stComFilter, 3 = "OR " Then

            strComFilter = Left(strComFilter, Len(strComFilter) - 4)

       End If

       strWhere = strWhere & strComFilter & ") AND "

    End If

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 23, 2017, at 1:36 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 John, 

If this is what you are talking about I can seem to get it to work correctly but I will try one more time...

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

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Monday, May 22, 2017 10:27 AM
Subject: Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

Art-

Did you not get my earlier reply?  Here it is again:

————————————————————————————

Art-

Your code doesn't match what I've been telling you to do, and in no way could you get:

([ClassificationType] LIKE '*Rental*') OR ([ClassificationType] LIKE '*Tax Credit*'

.. because there's no OR in your code now at all!

Are you sure you copied and pasted the current code?  And where are your date filters?

The code as shown below will NEVER work if a user selects more than one option in a group.  For example, a user might check 1 and 2 under Room Size, and your code will generate:

([BedroomCount] = 1 ) AND ([BedroomCount] = 2 ) 

BedroomCount cannot be both 1 AND 2 at the same time, so the user will get nothing.  THAT's why I had you code the complex OR code.

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 22, 2017, at 5:19 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

So does any one have ideas why this would occur. I don't think the syntax is different between and AND and a OR...?? 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Friday, May 19, 2017 8:07 AM
Subject: Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

OK. I went another way with it... But I end up with an error if I replace the AND with an OR at the end of the string...

Code Now:

 '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
    Dim strDisplayWhere As String
    Dim strComFilter As String
    Dim strClassFilter As String
    
    'Length of the criteria string appended to
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
  
      
    If Not IsNull(Me.txtFilterProjectNumber) Then
        strWhere = strWhere & "([Project Number] Like ""*" & Me.txtFilterProjectNumber & "*"") 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

       
    'Classification Type
    If Me.chkRental = -1 Then
        strWhere = strWhere & "([ClassificationType] LIKE '*Rental*') AND "
  '  ElseIf Me.chkRental = 0 Then
  '      strWhere = strWhere & "([ClassificationType] = '""') AND "
    End If
    
    If Me.chkTaxCredit = -1 Then
        strWhere = strWhere & "([ClassificationType] LIKE '*Tax Credit*') AND "
   ' ElseIf Me.chkTaxCredit = 0 Then
   '     strWhere = strWhere & "([ClassificationType] = '""') AND "
    End If
       
    If Me.chkMutualHelp = -1 Then
        strWhere = strWhere & "([ClassificationType] LIKE '*Mutual Help*') AND "
    'ElseIf Me.chkMutualHelp = 0 Then
    '    strWhere = strWhere & "([ClassificationType] = '""') AND "
    End If
    
    If Me.chkTK3 = -1 Then
        strWhere = strWhere & "([ClassificationType] LIKE '*TK3 Mutual Help*') AND "
    'ElseIf Me.chkTK3 = 0 Then
    '    strWhere = strWhere & "([ClassificationType] = '""') AND "
    End If
       
       
    'Community Name
    If Me.chkBearCreek = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Bear Creek*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
    If Me.chkBlackfoot = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Black Foot*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
  
    If Me.chkBridger = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Bridger*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
    If Me.chkCherryCreek = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Cherry Creek*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
   If Me.chkDupree = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Dupree*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
   
  If Me.chkEagleButte = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Eagle Butte*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
   If Me.chkGreenGrass = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Green Grass*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
   
   If Me.chkIronLightning = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Iron Lightning*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
   If Me.chkIsabel = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Isabel*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
   If Me.chkLantry = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Lantry*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

   If Me.chkLaPlante = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*LaPlante*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

   If Me.chkParade = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Parade*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

   If Me.chkRedScaffold = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Red Scaffold*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

   If Me.chkRidgeview = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Ridgeview*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

    If Me.chkSwiftbird = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Swiftbird*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

    If Me.chkTakini = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Takini*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

 
    If Me.chkThunderButte = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Thunder Butte*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
 
     If Me.chkTImberLake = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Timber Lake*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

    If Me.chkWhitehorse = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*White Horse*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If

    If Me.chkPromise = -1 Then
        strWhere = strWhere & "([CommunityName] LIKE '*Promise*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
 
  'Room Size
  
   If Me.chkOne = -1 Then
        strWhere = strWhere & "([BedroomCount] = 1 ) AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
    
    
   If Me.chkTwo = -1 Then
        strWhere = strWhere & "([BedroomCount] = 2 ) AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
    End If
 
  If Me.chkThree = -1 Then
        strWhere = strWhere & "([BedroomCount] = 3 ) AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
  End If
    
   If Me.chkFourPlus = -1 Then
        strWhere = strWhere & "([BedroomCount] = 4 ) AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
   End If
    
  'Unit Status
  
     If Me.chkDestroyed = -1 Then
        strWhere = strWhere & "([UnitStatus] LIKE '*Destroyed*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
   End If
 
  If Me.chkOccupied = -1 Then
        strWhere = strWhere & "([UnitStatus] LIKE '*Occupied*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
   End If
    
    If Me.chkVacant = -1 Then
        strWhere = strWhere & "([UnitStatus] LIKE '*Vacant*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
   End If
    
    If Me.chkTransferred = -1 Then
        strWhere = strWhere & "([UnitStatus] LIKE '*Transferred*') AND "
  '  ElseIf Me.chkBearCreek = 0 Then
  '      strWhere = strWhere & "([CommunityName] = '""') AND "
   End If

  If (Me.chkNAHASDA) = -1 Then
        strWhere = strWhere & "([NAHASDAFlag] = True) AND "
   
    End If
    
    If (Me.chkAMERIND) = -1 Then
        strWhere = strWhere & "([AmerIndFlag] = True) AND "
   
    End If

    If (Me.chkHandicap) = -1 Then
        strWhere = strWhere & "([HandicappedFlag] = True) AND "
    
    End If

   If (Me.chkActive) = -1 Then
        strWhere = strWhere & "([Active] = True) 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 filter."
    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 strWHERE string is returning:

([ClassificationType] LIKE '*Rental*') OR ([ClassificationType] LIKE '*Tax Credit*'

But the error is:

Run-time error '3075'

Missing ),]. or Item in query expression '([ClassificationType] LIKE '*REntal*') OR ([ClassificationType] LIKE '*Tax Credit*'.

But as soon as I turn the OR to an AND it works fine but obviously the wrong data is being returned.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Thursday, May 18, 2017 10:43 AM
Subject: Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

 

You missed one - AGAIN!

       If Not IsNull(Me.chkCherryCreek) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Cherry Creek*')"
       End If

       strWhere = strWhere & strClassFilter & strComFilter & ") AND "

    End If

strComFilter!

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 18, 2017, at 5:45 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 John,

OK so after getting my attention :) I have done the following:

'Classification Type
     If Me.chkRental Or Me.chkTaxCredit Or Me.chkMutualHelp Or Me.chkTK3 Then
       strClassFilter = "("
       
       If Not IsNull(Me.chkRental) Then
           strClassFilter = strClassFilter & "(ClassifcationType LIKE '*Rental*') OR "
       End If

       If Not IsNull(Me.chkTaxCredit) Then
           strClassFilter = strClassFilter & "(ClassifcationType LIKE '*Tax Credit*') OR "
       End If

       If Not IsNull(Me.chkMutualHelp) Then
           strClassFilter = strClassFilter & "(CommunityName LIKE '*Mutual Help*')"
       End If

        If Not IsNull(Me.chkTK3) Then
           strClassFilter = strClassFilter & "(CommunityName LIKE '*TK3 Mutual Help*')"
        End If

       strWhere = strWhere & strClassFilter & ") AND "

      End If
    

   If Me.chkBearCreek Or Me.chkBlackfoot Or Me.chkBridger Or Me.chkCherryCreek Then
       strComFilter = "("
       If Not IsNull(Me.chkBearCreek) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Bear Creek*') OR "
       End If

       If Not IsNull(Me.chkBlackfoot) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Black Foot*') OR "
       End If

       If Not IsNull(Me.chkBridger) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Bridger*')"
       End If
       
       If Not IsNull(Me.chkCherryCreek) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Cherry Creek*')"
       End If

       strWhere = strWhere & strClassFilter & strComFilter & ") AND "

    End If

This is what strWhere is returning....

?str?strwhere
((ClassifcationType LIKE '*Rental*') OR ) AND ((ClassifcationType LIKE '*Rental*') OR ((CommunityName LIKE '*Bear Creek*') OR )
((CommunityName LIKE '*Bear Creek*') OR ()

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Thursday, May 18, 2017 9:06 AM
Subject: Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

Art-

Pay attention!  In the first group, you start out using strClassFilter, but then you concatenate using strComFilter.

           strClassFilter = strComFilter & "(ClassifcationType LIKE '*Rental*') OR "

That's why you're missing an ending close paren.

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 18, 2017, at 4:45 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I think I am getting close:

'Text field example. Use quotes around the value in the string
     
     'Classification Type
     If Me.chkRental Or Me.chkTaxCredit Or Me.chkMutualHelp Or Me.chkTK3 Then
       strClassFilter = "("
       
       If Not IsNull(Me.chkRental) Then
           strClassFilter = strComFilter & "(ClassifcationType LIKE '*Rental*') OR "
       End If

       If Not IsNull(Me.chkTaxCredit) Then
           strClassFilter = strComFilter & "(ClassifcationType LIKE '*Tax Credit*') OR "
       End If

       If Not IsNull(Me.chkMutualHelp) Then
           strClassFilter = strComFilter & "(CommunityName LIKE '*Mutual Help*')"
       End If

        If Not IsNull(Me.chkTK3) Then
           strClassFilter = strComFilter & "(CommunityName LIKE '*TK3 Mutual Help*')"
        End If

       strWhere = strWhere & strClassFilter & ") AND "

      End If
    

   If Me.chkBearCreek Or Me.chkBlackfoot Or Me.chkBridger Then
       strComFilter = "("
       If Not IsNull(Me.chkBearCreek) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Bear Creek*') OR "
       End If

       If Not IsNull(Me.chkBlackfoot) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Black Foot*') OR "
       End If

       If Not IsNull(Me.chkBridger) Then
           strComFilter = strComFilter & "(CommunityName LIKE '*Bridger*')"
       End If

       strWhere = strWhere & strClassFilter & ") AND "

    End If
     

I need to add strComFilter into the last line to add it to the strWhere string. I put in the ISNull because anything I did not check returned and error about "invalid Use of Null". The error that I am currently getting is:

RUn-time error '3075':

Extra ) in query expression '(ClassificationType Like '*Rental*') OR )'.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Thursday, May 18, 2017 7:15 AM
Subject: Re: [MS_AccessPros] DYnamicall Searching Several fields VBS

Art-

No, leave the room boxes separate and use code similar to what I showed you for locations.

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 18, 2017, at 2:32 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, 

I knew you would be the one to sort that out. THe community checkboxes work independently of each other. I f the user wants to search for units in Bear Creek and Bridger or they could just search for units in Black foot. Any combination could happen. 

The Bedroom count was old code. It is now setup as individual check boxes (eg. chkOne, chkTwo, chkThree, etc.) again it should have the same independent function as the community checkboxes. 

Would it be better to use them as a option group? My understanding of an option group is only one and can be used at a time. In my case I need multiples....

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Thursday, May 18, 2017 2:44 AM
Subject: 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 (17)

---

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

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___