Search problem

[jackie77]

Hi all

I am still having problems adapting the search code placed, very kindly on this forum by Gromit. When I run the search none of the results populate even when I know there is a match

The code I am using is

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    
 

    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    
    ' Check for LIKE Booked Date
    If Me.txtBooked_Date > "" Then
        varWhere = varWhere & "[BookedDate] Like """ & Me.txtBooked_Date & " * "" And "
    End If
    
    ' Check for Engineer
    If Me.cmbEngineer > 0 Then
        varWhere = varWhere & "[Engineer] = " & Me.cmbEngineer & " And "
    End If
    
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        
         End If
    
    BuildFilter = varWhere
    
End Function

Can any one help as I seem to be getting no where with this, as you can probably tell am new to all this

Any advise would be most appricated

Thanks

Jackie

 

[pbaldy]

For starters, I wouldn't use Like with a date, and presuming it's actually a date/time field you need to surround it with #, so try:

varWhere = varWhere & "[BookedDate] = #" & Me.txtBooked_Date & "# And "

 

[jackie77]

Thanks Paul for the reply

I changed the code but it still does not so any reports

any other suggestions?

Jackie

 

[pbaldy]

Well, let's learn how to fish. Add this line:

Debug.Print varWhere
BuildFilter = varWhere

The debug line will put the result of the code in the Immediate window so you can examine it. Also, it would help to see how you call this function.

 

[jackie77]

Hi Paul

I got it to work thankfully!!!

I managed to get the search to work on the engineer criteria but not the date, when I replaced the code I previously had it works ok now, can I just ask a silly question you said you would not use Like for a date can I just ask why? would you suggest I keep working on it to find better coding for the date field?

Thanks
Jackie

 

[pbaldy]

Like is typically used for non-exact matches, like if I want to find all "Paul", whether it's Paul Smith or Paul Jones or whatever. There's no point in using it on a date. I believe = is more efficient than Like as well.

Are you saying it's working now, or not? If it works with the quotes, then your field must be a text field rather than a date/time field.

 

[jackie77]

Yes its working now fine andt it is set as a general date, I am using access 2007 so I not sure if that makes a difference?

Thanks again for your advise