ActiveX Date Picker Does Not Pass Values to Query

CFMI
CFMI used Ask the Experts™
on
I'm trying to run a query based on a form's text box.  I created the text box from Access' When running, I receive an error from the form:  "The Microsoft Access database engine does not recognize 'Forms![Reports Selection Form]![DTPicker7_Fr]' as a valid field name or expression."

I have other database files wherein the process works.  Any thoughts what might be the cause of the error?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
as per the error, it doesn't look like an ActiveX at all.
it seems like the Query is looking for a field "DTPicker7_Fr" on a form "Reports Selection Form" and one of the reasons it doesn't find it is because it's closed. in order to reference a form' field in a query, the form must be open
CFMIFinancial Systems Analyst

Author

Commented:
Thanks for your comment.  Actually the form is open but you're correct, the field "DTPicker7_Fr" on the 'Reports Selection Form' isn't seen or recognized.  I've moved the criterion "<[Forms]![Reports Selection Form]![DTPicker7_Fr]))" from WHERE to GROUP BY but it doesn't matter on this query:

SELECT DISTINCTROW [Balances 1_Crosstab].[Activity Date], [Balances 1_Crosstab].[Provider Name], [Balances 1_Crosstab].[Provider Number], Sum([Balances 1_Crosstab].[Total Of Balance]) AS [Total Of Balance], Sum([Balances 1_Crosstab].[100]) AS 100, Sum([Balances 1_Crosstab].[300]) AS 300, Sum([Balances 1_Crosstab].[311]) AS 311, Sum([Balances 1_Crosstab].[316]) AS 316 INTO tbl_Balances
FROM [Balances 1_Crosstab]
WHERE ((([Balances 1_Crosstab].[Activity Date])<[Forms]![Reports Selection Form]![DTPicker7_Fr]))
GROUP BY [Balances 1_Crosstab].[Activity Date], [Balances 1_Crosstab].[Provider Name], [Balances 1_Crosstab].[Provider Number]
HAVING (((Sum([Balances 1_Crosstab].[Total Of Balance]))<>0));

I do have a work-around however.  The above query without the WHERE clause allows me to generate the table from which the report is derived.  Then I have to run a second "DELETE" query with the WHERE clause ">[Forms]![Reports Selection Form]![DTPicker7_Fr]))".  That works. . . . but why doesn't the above work?

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