search form

**D R** · 11-12-2011, 12:03 PM

Hi. I need a little help. I have a form to search by date. I have two input fields: startdate, enddate. THIS IS MY CODE

Code:

If Me.Txtstartdate > "" Then
        varWhere = varWhere & "[start_date] >= #" & Day(Me.Txtstartdate.Value) & "/" & Month(Me.Txtstartdate.Value) & "/" & Year(Me.Txtstartdate.Value) & "#"
    End If
    
   
    If Me.Txtenddate > "" Then
        varWhere = varWhere & "[end_date] <= #" & Day(Me.Txtenddate.Value) & "/" & Month(Me.Txtenddate.Value) & "/" & Year(Me.Txtenddate.Value) & "#"
    End If

works well, except when I enter values for both fields

**pbaldy** · 11-12-2011, 01:28 PM

There would need to be " And " between the two conditions.

**D R** · 11-14-2011, 10:40 AM

thanks, but this doesn't work

Code:

If Me.Txtstartdate > "" Then
        varWhere = varWhere & "[start_date] >= #" & Day(Me.Txtstartdate.Value) & "/" & Month(Me.Txtstartdate.Value) & "/" & Year(Me.Txtstartdate.Value) & "#""AND"
    End If
      
     
    If Me.Txtenddate > "" Then
        varWhere = varWhere & "[end_date] <= #" & Day(Me.Txtenddate.Value) & "/" & Month(Me.Txtenddate.Value) & "/" & Year(Me.Txtenddate.Value) & "#"
    End If

So where do I put "and"? Sorry, I'm a complete beginner in Access..

**pbaldy** · 11-14-2011, 11:02 AM

It would look like this:

varWhere = varWhere & "[start_date] >= #" & Day(Me.Txtstartdate.Value) & "/" & Month(Me.Txtstartdate.Value) & "/" & Year(Me.Txtstartdate.Value) & "# AND "

You would have to account for the user only choosing one date.

**ssanfu** · 11-14-2011, 11:30 AM

Here is an alternative:

Code:

    If Me.Txtstartdate > "" Then
        varWhere = varWhere & "[start_date] >= #" & Format(Me.Txtstartdate, "dd/mm/yyyy") & "# AND "
    End If

    If Me.Txtenddate > "" Then
        varWhere = varWhere & "[end_date] <= #" & Format(Me.Txtenddate, "dd/mm/yyyy") & "#"
    Else
        'end date not entered - remove the last 5 char.... ie " AND "
        varWhere = Left(varWhere, Len(varWhere) - 5)
    End If

**D R** · 11-14-2011, 12:34 PM

thank you both.

Originally Posted by ssanfu

Here is an alternative:

Code:

Else    
    varWhere = Left(varWhere, Len(varWhere) - 5)
End If

This is great!

**D R** · 11-14-2011, 01:37 PM

Not so great actually. When I try to do my search by other criteria (not date), this line:

Code:

varWhere = Left(varWhere, Len(varWhere) - 5)

causes the error: "Invalid use of null"

**ssanfu** · 11-14-2011, 01:55 PM

Originally Posted by D R

Not so great actually. When I try to do my search by other criteria (not date), this line:

Code:

varWhere = Left(varWhere, Len(varWhere) - 5)

causes the error: "Invalid use of null"

What other criteria??? It works for the tiny bit of code you provided. If there is more code (other criteria), then you will have to modify the code yourself or post it for us to see.

**D R** · 11-14-2011, 03:09 PM

Yes.I should have to mention it earlier. Sorry

Code:

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varType As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  
    varType = Null  
    
    
    If Me.TxtNumber > "" Then
        varWhere = varWhere & "[number] LIKE """ & Me.Txtnumber & "*"" AND "
    End If
    
    If Me.Txtsubject > "" Then
        varWhere = varWhere & "[subject] LIKE """ & Me.Txtsubject & "*"" AND "
    End If
      
    If Me.Txtstartdate > "" Then
        varWhere = varWhere & "[start_date] >= #" & Day(Me.Txtstartdate.Value) & "/" & Month(Me.Txtstartdate.Value) & "/" & Year(Me.Txtstartdate.Value) & "# And"
  End If
  
    
    If Me.Txtenddate> "" Then
        varWhere = varWhere & "[end_date] <= #" & Day(Me.Txtenddate.Value) & "/" & Month(Me.Txtenddate.Value) & "/" & Year(Me.Txtenddate.Value) & "#"
  
   Else
   
  varWhere = Left(varWhere, Len(varWhere) - 4)
      End If
    
   
    If Me.Txtsender > "" Then
        varWhere = varWhere & "[sender] LIKE """ & Me.Txtsender & "*"" AND "
    End If
    
 
    If Me.Txtlocation > "" Then
        varWhere = varWhere & "[location] LIKE """ & Me.Txtlocation& "*"" AND "
    End If
    
       For Each varItem In Me.Lsttypes.ItemsSelected
        varType = varType & "[types] = """ & _
                    Me.LstTypes.ItemData(varItem) & """ OR "
        
    Next
    
    
    If IsNull(varType) Then
       
    Else
        
        If Right(varType, 4) = " OR " Then
            varType = Left(varType, Len(varType) - 4)
        End If
 
       
        varWhere = varWhere & "( " & varType & " )"
    End If
    
 
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
         
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
     
    BuildFilter = varWhere
    
End Function

**pbaldy** · 11-14-2011, 05:10 PM

You don't need that line, as you've already accounted for the " AND " in your code at the end. It would error earlier if nothing had been selected yet.

**D R** · 11-14-2011, 05:48 PM

I done this way

Code:

If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
         
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        If Right(varWhere, 6) = "# AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
     End If

and now it works. Thanks a lot.

**pbaldy** · 11-14-2011, 05:57 PM

The second test (with the #) makes no sense. It will never be true, as the test before will have trimmed off the " And ". I would delete that block.

**D R** · 11-14-2011, 06:37 PM

LOL, you're right. It may be that I made a mistake when I typing this way:
"# And" and not "# And "

but it worked with that line

**ssanfu** · 11-14-2011, 09:56 PM

Does this work?

Code:

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varType As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null
    varType = Null

    If Me.Txtnumber > "" Then
        varWhere = varWhere & "[number] LIKE """ & Me.Txtnumber & "*"" AND "
    End If

    If Me.Txtsubject > "" Then
        varWhere = varWhere & "[subject] LIKE """ & Me.Txtsubject & "*"" AND "
    End If

    If Me.Txtstartdate > "" Then
'          I would use:    Format(Me.Txtstartdate, "dd/mm/yyyy")
        varWhere = varWhere & "[start_date] >= #" & Day(Me.Txtstartdate.Value) & "/" & Month(Me.Txtstartdate.Value) & "/" & Year(Me.Txtstartdate.Value) & "# AND "
    End If

    If Me.Txtenddate > "" Then
        varWhere = varWhere & "[end_date] <= #" & Day(Me.Txtenddate.Value) & "/" & Month(Me.Txtenddate.Value) & "/" & Year(Me.Txtenddate.Value) & "# AND "
    End If

    If Me.Txtsender > "" Then
        varWhere = varWhere & "[sender] LIKE """ & Me.Txtsender & "*"" AND "
    End If

    If Me.Txtlocation > "" Then
        varWhere = varWhere & "[location] LIKE """ & Me.Txtlocation & "*"" AND "
    End If

    For Each varItem In Me.LstTypes.ItemsSelected
        varType = varType & "[types] = """ & Me.LstTypes.ItemData(varItem) & """  OR "
    Next

    If Not IsNull(varType) Then
        If Right(varType, 4) = " OR " Then
            varType = Left(varType, Len(varType) - 4)
        End If
        varWhere = varWhere & "( " & varType & " )"
    End If

    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere

        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If

    BuildFilter = varWhere

End Function

**D R** · 11-15-2011, 07:41 AM

yes, it works just fine

search form

Thread Tools

search form

Similar Threads

Search Form

Search Form Help

using form button, search table (via form), find/return info to form

Search box on a form.

Search on in a form

Posting Permissions