By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Form Filter doesn't work for all data

P: 47
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
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,627
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 15k+
P: 31,433
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

P: 47
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.