Totals Box on Form not showing summary from a Date Range in query.. IIF? DLookUp?

[AccessForumUser]

Hello, I'm not sure which function to use, either IIF or DLookUp function to show the summary of a column in a query on a form text box. The problem is that as soon as I put a date range into the query, the form errors out as soon as the form is run.. After I put the date ranges in, only my charts display the correct information. My code for the charts are SELECT [MYQUERYCOLUMNNAME], Count(*) AS [Count] FROM [MYQUERYNAME] GROUP BY [MYQUERYCOLUMNNAME]



If I take the date range out from my query, it shows everything for the entire database based on another code i was using.. i know there are two different control source codes I would need for referencing each of them, but I'm having trouble displaying the results from a date range to my text box.

-the date range will never show a blank result

I was thinking IIF for comparing start/end times with my column, but dlookup may work as well..

so, which function would work the best for me? any ideas?

Thanks!

[June7]

Count is a reserved word - should not use reserved words as names.

Use aggregate Sum() function to total a field for form's RecordSource. If you want to use domain aggregate function in textbox expression, I think it would be DSum, not DLookup.

Post the query attempting the date filtering.

The query is actually named MYQUERYNAME? This query is the data source for form and chart?

[AccessForumUser]

the query is not labeled myqueryname.. i was just using it as an example of what I have for my charts.
the query is the resulting data source for the form only because I have a start and finish range to deal with.. pulling from my table works fine, but date range in query messes up everything unfortunately.. but it's necessary for what i'm doing..

I'm playing with DSum now.. I'll post if it works.. thanks again.

[June7]

I never use dynamic parameterized queries. I prefer VBA to construct filter criteria and apply to form Filter property or WHERE CONDITION argument of OpenForm and OpenReport commands. Review http://allenbrowne.com/ser-62.html