Access Field Control Source to filter by current Day.

carlspywell
carlspywell used Ask the Experts™
on
I want to add a text box with a control source that only pulls back a count for information entered today. I have the following code but it returns #Error

=DCount("*","STATS","Filter1 = 'filter1' and DAY(DateReceived) = DAY(NOW())")

If i use DAY(DateReceived) = '1' it returns 5 (which is correct count for the 1st)

It's the DAY(Now()) that's causing the issue.

I cant put the filter on the main query as I also have monthly total text boxes on the form.

any ideas?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use Day(GetDate()) function instead of day(now())

Author

Commented:
Same Result!
Sorted it, It's in the formatting!

=DCount("*","STATS","Filter1 = 'filter1' and DAY(DateReceived) = '" & DAY(NOW()) & "'")

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial