You missed one - AGAIN!
If Not IsNull(Me.chkCherryCreek) Then
strComFilter = strComFilter & "(CommunityName LIKE '*Cherry Creek*')"
End If
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 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
strClassFilter = strClassFilter & "(ClassifcationType LIKE '*Tax Credit*') OR "
End If
If Not IsNull(Me.chkMutualHelp) Then
strClassFilter = strClassFilter & "(CommunityName LIKE '*Mutual Help*')"
End If
strClassFilter = strClassFilter & "(CommunityName LIKE '*Mutual Help*')"
End If
If Not IsNull(Me.chkTK3) Then
strClassFilter = strClassFilter & "(CommunityName LIKE '*TK3 Mutual Help*')"
End If
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
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
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 ()
((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
'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
strClassFilter = strComFilter & "(ClassifcationType LIKE '*Tax Credit*') OR "
End If
If Not IsNull(Me.chkMutualHelp) Then
strClassFilter = strComFilter & "(CommunityName LIKE '*Mutual Help*')"
End If
strClassFilter = strComFilter & "(CommunityName LIKE '*Mutual Help*')"
End If
If Not IsNull(Me.chkTK3) Then
strClassFilter = strComFilter & "(CommunityName LIKE '*TK3 Mutual Help*')"
End If
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
strComFilter = strComFilter & "(CommunityName LIKE '*Black Foot*') OR "
End If
If Not IsNull(Me.chkBridger) Then
strComFilter = strComFilter & "(CommunityName LIKE '*Bridger*')"
End If
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:
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;
FROM tblUnit LEFT JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID;
The form also contains a command button called cmdFIlter, its code is as follows:
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
'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
'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
__._,_.___
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