467,119 Members | 708 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,119 developers. It's quick & easy.

Form Filter doesn't work for all data

Hi
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
  1. Private Sub DateSearchBTn_Click()
  2. Dim DateFilter As String
  3. If Me.EndDate = "" Then _
  4. DateFilter = "([AlmDate]=#" & Me.StartDate & "#)"
  5. Form_AlmActiveQrySform.Filter = DateFilter
  6. Form_AlmActiveQrySform.FilterOn = True
  7. Debug.Print DateFilter
  8. If Me.EndDate <> "" Then
  9. DateFilter = "([AlmDate] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"
  10. Form_AlmActiveQrySform.Filter = DateFilter
  11. Form_AlmActiveQrySform.FilterOn = True
  12. Debug.Print DateFilter
also here is the code from the query where the form is based
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM ALARMHISTORY
  3. 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
  4. 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
Sep 17 '11 #1
  • viewed: 2592
Share:
3 Replies
ADezii
Expert 8TB
Try:
Expand|Select|Wrap|Line Numbers
  1. Dim DateFilter As String
  2.  
  3. If Me.EndDate = "" Then
  4.   DateFilter = "([AlmDate]=#" & Me.StartDate & "#)"
  5. Else
  6.   DateFilter = "([AlmDate] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"
  7. End If
  8.  
  9. With Form_AlmActiveQrySform
  10.   .Filter = DateFilter
  11.   .FilterOn = True
  12. End With
Sep 17 '11 #2
NeoPa
Expert Mod 16PB
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 (#)).
Sep 26 '11 #3
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
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 13 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Google Mike | last post: by
2 posts views Thread by Larry | last post: by
2 posts views Thread by Brett Porter | last post: by
1 post views Thread by VMI | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.