Form Filter doesn't work for all data

[rwalle]

Hi
I have a strange problem in a form filter my set up is as follows
there is a form named "InAlarmResumeF orm" that has a subform "AlmActiveQrySf orm" the form has Textboxes with datapicker enabled "StartDate" and "EndDate" for the user to pick dates to apply to the subform filter.
the way this works is , if the user pick a date in the "StartDate" TextBox and left empty the "EndDate", the filter looks for register with the "StartDate" as its date if the user fill the "StartDate" and "EndDate" TextBoxes the filter will bring data between 2 dates, it seems to work . . but reviewing all the data I have in the query where the Subform is based , I can see data from dates between 12/09/11 to 15/09/11 ,and using the form filters I can make it works only for dates from 13/09/11 to 15/09/11, if I pick a single date from 06/09 to 12/09 the form returs zero records
If I use the 2 date boxes with dates in this range (06/09 to 12/09) the form returns data from the 09/09 to the high limt I set on the End date
If I choose the end date in the "suspected" range it will return zero records

here is the code for the Button that trigger the filter

Private Sub DateSearchBTn_Click()
Dim DateFilter As String
If Me.EndDate = "" Then _
DateFilter = "([AlmDate]=#" & Me.StartDate & "#)"
Form_AlmActiveQrySform.Filter = DateFilter
Form_AlmActiveQrySform.FilterOn = True
Debug.Print DateFilter
If Me.EndDate <> "" Then
DateFilter = "([AlmDate] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"
Form_AlmActiveQrySform.Filter = DateFilter
Form_AlmActiveQrySform.FilterOn = True
Debug.Print DateFilter

also here is the code from the query where the form is based

SELECT ALARMHISTORY.Al_Start_Time, CInt(Nz((Sum(DateDiff("n",[Al_start_Time],[Al_norm_Time]))),0)) AS AlmActiveTime, Format([Al_Start_Time],"dd/mm/yyyy") AS AlmDate, Format((Right([Al_Start_Time],13)),"Short Time") AS AlmTime, ALARMHISTORY.Al_Tag, ALARMHISTORY.Al_Norm_Time
FROM ALARMHISTORY
GROUP BY ALARMHISTORY.Al_Start_Time, Format([Al_Start_Time],"dd/mm/yyyy"), Format((Right([Al_Start_Time],13)),"Short Time"), ALARMHISTORY.Al_Tag, ALARMHISTORY.Al_Norm_Time
HAVING (((ALARMHISTORY.Al_Norm_Time) Is Not Null));

The AlmDate field wich is the one I'm filtering in the Query comes from a timestamp field [Al_Start_Time] that is formated as dd/mm/yyyy hh:nn:ss

hope some body can help

Best regards

Raymundo Walle

[ADezii]

Try:

Dim DateFilter As String
 
If Me.EndDate = "" Then
  DateFilter = "([AlmDate]=#" & Me.StartDate & "#)"
Else
  DateFilter = "([AlmDate] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"
End If
 
With Form_AlmActiveQrySform
  .Filter = DateFilter
  .FilterOn = True
End With

[NeoPa]

Controls never resolve to "". If there is no data entered then the control will be Null.

SQL dates are not in local format but in specific SQL Date format (See Literal DateTimes and Their Delimiters (#)).

[rwalle]

Thanks for the Answers ,
I think it is related with the date format what I did is before put dates in filters I formated them using

Format([AlmDate],"mm/dd/yyyy")

also did the same for the Field [Al_Start_Time] so both the field to search and the search box date are in the same format

Thanks