asked on

How to search by combo box selections

Good Day Experts

When I make a selection from any of the drop down fields and select search I do not received the desired results. For any selections made return records for the entire database.

What I trying to do is get just those records with the selection of the field to return in the datasheet. For instance if I select a name in the field Production Assigned for dates from 04/01/2010 and 05/01/2010, only those records would return in the datasheet or if I select just the name in that field all the records associated with that name will return.

I have included the code below.

If you have any ideas how to make this happen, it would be greatly appreciated.

Private Sub Search_Click()
    Const cInvalidDateError As String = "You have entered an invalid date."
    Dim sql As String
    sql = "select * from [tblWorkload Information Tracker]"
    Dim strWhere As String
    Dim strError As String
    
    strWhere = "1=1"
    
     ' If Production Assigned
    If Not IsNull(Me.ProductionAssigned) Then
        'Create Predicate
        strWhere = strWhere & " AND " & "[Production Assigned] = " & Me.ProductionAssigned
    End If
    
    ' If Status
    If Nz(Me.Status, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[Status] = " & Me.Status
    End If
           
    ' If QA Reviewer
    If Nz(Me.QandALead, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[QandALead] = " & Me.QandALead
    End If
    
     ' If Client ID
    If Nz(Me.ClientID, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[ClientID]= " & Me.ClientID
    End If
    
     ' If Work Type
    If Nz(Me.WorkType, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[WorkType] = " & Me.WorkType
    End If
    
     ' If Work Category
    If Nz(Me.WorkCategory, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[WorkCategory] = " & Me.WorkCategory
    End If
  
     ' If Request Type
    If Nz(Me.RequestType, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[RequestType] = " & Me.RequestType
    End If
    
     ' If Region
   If Nz(Me.Region, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Region = " & Me.Region
    End If
    
      'If Business Type
    If Nz(Me.BusinessType, "") <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "[BusinessType] = " & Me.BusinessType
    End If
   
    ' If Effective Date From
    If IsDate(Me.txtEffectiveDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "[EffectiveDate] >= #" & Me.txtEffectiveDateFrom & "#"
    ElseIf Nz(Me.txtEffectiveDateFrom, "") <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Effective Date To
    If IsDate(Me.txtEffectiveDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "DateValue([EffectiveDate]) <= #" & Me.txtEffectiveDateTo & "#"
    ElseIf Nz(Me.txtEffectiveDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Date Received From
    If IsDate(Me.txtDateReceivedFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "[DateReceived] >= #" & Me.txtDateReceivedFrom & "#"
    ElseIf Nz(Me.txtDateReceivedFrom, "") <> "" Then
        strError = cInvalidDateError
    End If
    
     ' If Date Received To
    If IsDate(Me.txtDateReceivedTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "DateValue([DateReceived]) <= #" & Me.txtDateReceivedTo & "#"
    ElseIf Nz(Me.txtDateReceivedTo, "") <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If DataTRAK Number
    If Nz(Me.txtDatatrakNumber, "") <> "" Then
        ' Add it to the predicate - match on leading characters
        strWhere = strWhere & " AND " & [DatatrakNumber] Like "*'" & Me.txtDatatrakNumber & "*'"
    End If
    
     
    If Not Me.FormFooter.Visible Then
           Me.FormFooter.Visible = True
           DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
    End If
    
    If Len(strWhere) > 3 Then
        Me.subfrmWITSearchDatasheet.Form.RecordSource = sql
        Me.subfrmWITSearchDatasheet.Form.Filter = strWhere
        Me.subfrmWITSearchDatasheet.Form.FilterOn = True
        
    End If
     
    Debug.Print strWhere
    
    If strError <> "" Then
        MsgBox strError
    End If
    
End Sub
Function GetDateFilter(dtDate As Date) As String
    ' Date filters must be in MM/DD/YYYY format
    GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#"
End Function

Open in new window

Search5.mdb

ASKER CERTIFIED SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Beeyen

ASKER

Good Day,

You don't know me, but I more than most (a whole lot of people) appreciate the earnest time and recommendations from you and LSM. I believe in one note from LSM he mentioned what might happen if I did what I just did and he was right.

I just lost 300 records of important information making the changes recommended. No ones fault but mine. I have been working with a of suggestion, but because of the dollars involved with the loss, I will have to spend the rest of the day and night trying to recover. I now have to continue what I started to implement the suggestions to make this db structurally sound, but until then I was hoping for a work around.

Dale Fye

The last line of my post that recommended making these changes recommended to "make backups" so you can work backwards. If you didn't do this, use one of the sample databases you posted here to recover the lost data.

Dale Fye

Your problems all go back to two things:

1. setting the datatype of the foreign key fields in your table to text when they should have been numeric

2. Using the Lookup tab and combo boxes to present data in tables. Every time I see someone using this technique I encourage them to get rid of the Lookups and write queries that perform the same action. If you open a table and see "some text string" in a field, you are going to think that this field is a text field, so when you try to query it, you will look for the text string. But if you are using the LOOKUP "feature", what you see is not really the value of that field. This is confusing to most people, especially the novices that it was designed to help.

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Beeyen

ASKER

Good Day Ma'am

Thank you for sharing this with me,

Your search form is excellent! I looked for a search form on line but could not fine one suitable. I wish I had this as an example while creating the one I am finishing. I now better understand what some of what the other EE's were trying to get across to me with respect to cbo and txt.

In a couple of days I will be attempting to create some sort of all print form. One that would allow for printing forms with tabs and the forms within those tabs from a main form. So far, from what I can find, I will have to create (copy and paste the forms in a rpt and then modify it for printing, as most if not all printing is done from rpt's.

Thanks again!

UnifiedIS

Some of us had no idea that the author was double-posting and made an effort to assist. Effort should be rewarded and rudeness should have a cost.

Dale Fye

Concur with UnifiedIS. If, as he says above: "I now better understand what some of what the other EE's were trying to get across to me with respect to cbo and txt."

Then Helen should at least be awarded a reasonable number of points for providing an example.