Search Form using Vb

[bnar]

Hi Friends

i have a database with a search form created using Vb code..query name is (qrySearch) and the form name is (frmSearch) search works fine except for OrderDate field which i want to give it 2 dates and query returns data between those 2 dates..any help please

[Robeen]

Are you saying you want to add Code to Function BuildFilter() so that it will return data between textBeforeDate and textAfterDate?

Wait . . . I just noticed that you have the code in there - but it's probably not working - right?
That's because when you work with dates - you have to use "#" on both sides of the date in order for Access to recognize it as a Date.

[Robeen]

Try something like this:

    'Check for Start Date
    If Not IsNull(Me.txtAfterDate) Then ' 
        varWhere = varWhere & "[OrderDate] Between #" & Me.txtAfterDate & "# AND "
    End If
    
    'Check for End Date
    If Not IsNull(Me.textBeforeDate) Then 
        varWhere = varWhere & "#" & Me.textBeforeDate & "#" & " AND "
    End If

[bnar]

tryed you code it gives this error when tyoing twi dates,,also other search fields does not work.


but i found this code works perfectly...(if you have time to make your code work also it will better)Thanks

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

[ssanfu]

"Value" is not required - it is the default property (saves on typing)

You have to remove the last "AND" in the WHERE string:

   If IsDate(Me.txtAfterDate) Then

      varWhere = varWhere & "[orderdate] >= #" & Me.txtAfterDate & "# AND "
   End If

   If IsDate(Me.textBeforeDate) Then
      varWhere = varWhere & "[orderdate] <= #" & Me.textBeforeDate & "# AND "
   End If

   ' remove the last AND
   If Len(Trim(varWhere)) > 0 Then
      varWhere = Left(varWhere, Len(varWhere) - 5)
   End If

   'for debugging
   MsgBox varWhere

[bnar]

Thanks a lote ssanfu it works great.
Sorry about mistake in my previous post, its pasted twice it's just one statement for each (

Me.txtAfterDate And Me.textBeforeDate)