Kamis, 18 Mei 2017

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 
(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 
(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 
(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 
(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 (9)

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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar