Good morning all, again thank you for taking the time to help me muddle through this and I apologize if I wasn't clear from the beginning. Just to clarify a few things, what I'm trying to get Access to do is basically opposite of what it's set up to do...Contacts being the main table/form, Bids the sub, one Contact to potentially many Bids. In the Bid table exists 3 fields that an estimator may need to filter by (possibly all, or just 1), status, bidder, biddate. I need to filter those records and pull up the related Contact information (opposite of finding the contact and related bids). Criteria in my posted db would be: Status=Needs Bid, Status=Pending, Status=Accepted. Bid Date=6/21/15, Bid Date=6/23/15 Bid Date=6/26/15. Bidder=John Doe, Bidder=Ron Paul, Bidder=Sam Anderson.
This being said, I have dotted my I's and crossed my T's and the code behind my filter button is:
Code:
Private Sub filter_Click()
On Error GoTo errHandler
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
'Note: Only records matching ALL of the criteria are returned.
'Author: Allen Browne (allen@allenbrowne.com), June 2006.
'Option Compare Database
'Option Explicit
'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 to append 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.statusfilter) Then
strWhere = strWhere & "([status] = """ & Me.statusfilter & """) AND "
End If
If Not IsNull(Me.bidderfilter) Then
strWhere = strWhere & "([bidder] = """ & Me.bidderfilter & """) AND "
End If
If Not IsNull(Me.biddatefilter) Then
strWhere = strWhere & "([biddate] = " & Format(Me.biddatefilter, conJetDate) & ") 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.bidsheet.Form.filter = strWhere
Me.bidsheet.Form.FilterOn = True
End If
Debug.Print strWhere
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub
And when I enter Bidder=Sam Anderson, I get a blank Bid record. If I enter Status=Pending, I get a blank bid record. Same with biddate=10/26/15. I have Query16 that tests for data, and that pulls the proper information. Sigh.
Windows 7 64bit 
Access 2013
Reply With Quote
(I need to think faster and type slower)
