Working & Filtering Dates

[the_reg]

Hi Guys - i am trying to filter on a date 'txtDateSpudded' entered within a multi field input Form to display those results greater than the date entered ... i am having no luck getting this to work - please could someone help - thank you in advance
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
Dim cmbGeographic_Region As String
Dim cmbCountry As String

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Const strcDateConst = "\#mm\/dd\/yyy\#"

strDateField = "[DateSpudded]" ' field name for Date

tmp = """"


varWhere = Null ' Main filter
'varColor = Null ' Subfilter used for colors
' Check for Country
If Len(Me.cmbCountry) > 0 Then
varWhere = varWhere & "[Country] LIKE " & tmp & Me.cmbCountry & tmp & " AND "
End If

' Check for Company
If Len(Me.txtCompany) > 0 Then
varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
End If

' Check for Geographic Region
If Len(Me.cmbGeographic_Region) > 0 Then
varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.cmbGeographic_Region & tmp & " AND "
End If
' Filter on Date
If IsDate(Me.txtDateSpudded) Then
varWhere = "(" & strDateField & " >= " & Format(Me.txtDateSpudded, strcDateConst) & ")" & " AND "
End If

If Len(Me.txtCompany) > 0 Then
varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
End If
If Len(Trim(varWhere)) > 0 Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
BuildFilter = varWhere
End Function

[the_reg]

i manged to get the date to be recognised work with the amended code below ... but still wont filter the records !!!


Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
Dim cmbGeographic_Region As String
Dim cmbCountry As String

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Const strcDateConst = "mm/dd/yyyy" '"\mm\/dd\/yyy\"

strDateField = "[DateSpudded]" ' field name for Date

tmp = """"
varWhere = Null ' Main filter
'varColor = Null ' Subfilter used for colors
' Check for Country
If Len(Me.cmbCountry) > 0 Then
varWhere = varWhere & "[Country] LIKE " & tmp & Me.cmbCountry & tmp & " AND "
End If

' Check for Company
If Len(Me.txtCompany) > 0 Then
varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
End If

' Check for Geographic Region
If Len(Me.cmbGeographic_Region) > 0 Then
varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.cmbGeographic_Region & tmp & " AND "
End If
' Filter on Date
If IsDate(Me.txtDateSpudded) Then
varWhere = "(" & strDateField & " >= " & Format(Me.txtDateSpudded, strcDateConst) & ")"
End If

If Len(Me.txtCompany) > 0 Then
varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
End If
If Len(Trim(varWhere)) > 0 Then
varWhere = Left(varWhere, Len(varWhere))
End If
BuildFilter = varWhere
End Function

[pbaldy]

You can use this to see the finished string:

http://www.baldyweb.com/ImmediateWindow.htm

i don't think it's going to look like you expect.

also, date values should be surrounded by #.