Help Please!!!!!!!!

[fenerfan]

Hi,

When I filter a for linked to a query by date, the Date I input has a MAJOR problem.

for example if I enter 12/07/2006 into the textbox, and press the control button running the following code:


Me.SBFRM_FLUE_DUST.Form.RecordSource = "SELECT * FROM qry_flue_dust " & BuildFilter
Me.SBFRM_FLUE_DUST.Requery

End Sub


Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null
Dim DateStart As Date
DateStart = DateValue(Me.Date1)
DateStart = Format(ActiveXCtl7, "dd, mmm, yyyy")


If Me.Date1 > "" Then
varWhere = varWhere & "[DT_TI_STARTED] > #" & ActiveXCtl7 & "# AND "
End If



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

The problem is if the day is less than 12 it automically becomes the month.
Meaning when i put in 11/07/2006... (Meaning I want all results after 11/07/2006) the form returns records after 7/11/2006 instead.....

ActiveXCtl7 is a calender control.... but I have tried to hardcode a date into the sql code etc with the same result.... also this problem does not occur when I put a figure into the query builder.... but that is of know use to me...


Can someone please help....

Thanks in Advance.

 

[pbaldy]

This might be informative:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

In the future, a descriptive thread title will be more useful to people performing searches.