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: dbalorenzini@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar