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

Inaccurate Date filtering in subform

P: 31
I have a form with a subform and I need to filter the records in the subform by dates. (Access 2007)

The main form has two unbound text boxes called StartFilter and EndFilter. Both use the Datepicker feature. Both have the Now() function as the Default setting to limit records to today's date as the default setting. Both are set to the short date format. Both have a small procedure: DoCmd.Requery Me.Refresh. to refresh the data on change.

The underlying query has this in the relevant date field. >[Forms]![afrmStaffActivityCentre]![StartFilter] And <[Forms]![afrmStaffActivityCentre]![EndFilter]

It works to a point but the results can vary depending on the time of day. In other words, the time factor of the date fields is returning different results depending on the time of day. For example, if both fields are set to the same date. some records may not be included because the time factor in the Start and End Filter fields excludes some records with the same date because of the time when the records were created.

How can I overcome the time component in the date fields?
Oct 28 '08 #1
Share this Question
Share on Google+
4 Replies


P: 93
Hi

I'm not sure if it helps but try date() instead of now() which is date + time even if you set up short date the 'raw' data can still be date + time.

Regards
Emil
Oct 28 '08 #2

P: 31
Hi

I'm not sure if it helps but try date() instead of now() which is date + time even if you set up short date the 'raw' data can still be date + time.

Regards
Emil
Thanks Emil

I tried Date() but that doesn't work either. The Now() / Date() settings are only the default when the form is first opened and now it is the end of the day none of my dates for today are showing, whether by the default settings when the form is opened or if I select the dates using the datepicker.
Oct 28 '08 #3

P: 93
Make sure the table you query holds the date as date and not date + time.

Otherwise you can add extra function in your criterias like DateDif function this will will use only date (even if you have time).

Hope it helps
Emil
Oct 28 '08 #4

P: 31
Hi

I'm not sure if it helps but try date() instead of now() which is date + time even if you set up short date the 'raw' data can still be date + time.

Regards
Emil
I think I have found the problem! I had the Now() function as the default setting in the table itself, so if the date was added using the default setting, it was adding the time as well. I have changed the default setting to Date() and I it seems to working as it should.

I also changed the >....StartFilter and <....EndFilter to >=....StartFilter and <=...EndFilter and this is more accurately returning the results I need.

Thanks to all who offered suggestions.
Oct 29 '08 #5

Post your reply

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