Link to home
Start Free TrialLog in
Avatar of CFMI
CFMIFlag for United States of America

asked on

ActiveX Date Picker Does Not Pass Values to Query

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?
Avatar of Joseph Krausz
Joseph Krausz

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
Avatar of CFMI

ASKER

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?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.