I have a strange problem in a form filter my set up is as follows
there is a form named "InAlarmResumeForm" that has a subform "AlmActiveQrySform" 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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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));
hope some body can help
Best regards
Raymundo Walle