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

source Query works, Chart Doesnt

P: 37
I have a problem I have never seen before maybe someone can help. I wrote a query and everything was working fine, I then built a chart on it, still so far so good. Management decided they wanted to choose a date range on which to view the data. Next I added a front page form with two combo boxes for date1 and date2, then added a criteria to my query, still everything works (query opens fine and pulls data correctly). This is where the problem started. The chart will no longer open.

Expand|Select|Wrap|Line Numbers
  1. SELECT Format([received date],"mmmmyyyy") AS [date], [tbl-tag_master].Supplier, Sum(1) AS total
  2. FROM [tbl-tag_master]
  3. WHERE ((([tbl-tag_master].[Received Date]) Between [Forms]![Front]![Combo8] And [forms]![front]![combo10]))
  4. GROUP BY Format([received date],"mmmmyyyy"), [tbl-tag_master].Supplier;
  5.  
This is my query the only thing I added was the WHERE statement. The chart now gives the error below, but the query itself still opens fine.

I now get this error "The Microsoft Jet Database engine does not recognize '[forms]![front]![combo8]' as a valid field name or expression."

Any help is greatly appreciated.


Thanks,

Chad
Mar 4 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
I have a problem I have never seen before maybe someone can help. I wrote a query and everything was working fine, I then built a chart on it, still so far so good. Management decided they wanted to choose a date range on which to view the data. Next I added a front page form with two combo boxes for date1 and date2, then added a criteria to my query, still everything works (query opens fine and pulls data correctly). This is where the problem started. The chart will no longer open.

Expand|Select|Wrap|Line Numbers
  1. SELECT Format([received date],"mmmmyyyy") AS [date], [tbl-tag_master].Supplier, Sum(1) AS total
  2. FROM [tbl-tag_master]
  3. WHERE ((([tbl-tag_master].[Received Date]) Between [Forms]![Front]![Combo8] And [forms]![front]![combo10]))
  4. GROUP BY Format([received date],"mmmmyyyy"), [tbl-tag_master].Supplier;
  5.  
This is my query the only thing I added was the WHERE statement. The chart now gives the error below, but the query itself still opens fine.

I now get this error "The Microsoft Jet Database engine does not recognize '[forms]![front]![combo8]' as a valid field name or expression."

Any help is greatly appreciated.


Thanks,

Chad
Hi Chad. This is a known issue with the Access Jet database engine. References to controls on a form work fine on a select query but fail in aggregate queries (sometimes) and crosstab queries (always). To overcome this I substitute a function call that returns the control's value instead of by direct reference to the form on which the control is placed. Jet seems to work fine with function calls.

Place the following function definition code in a public code module of your database (under Modules in the database window). If you don't have any modules defined so far create a new one and save it under any suitable name.

Expand|Select|Wrap|Line Numbers
  1. Public Function FormFieldValue(FormName As String, FieldName As String)
  2.     FormFieldValue = Forms(FormName).Controls(FieldName)
  3. End Function
Then replace the references to the form fields in your query criteria cells with calls to the function above, like this:

Expand|Select|Wrap|Line Numbers
  1. Between FormFieldValue("Front", "combo8") And FormFieldValue("front", "combo10")
-Stewart
Mar 4 '08 #2

P: 37
Thanks a million, worked perfect!
Mar 6 '08 #3

Expert Mod 2.5K+
P: 2,545
Thanks a million, worked perfect!
Glad to have been able to help, Chad! -S
Mar 6 '08 #4

Post your reply

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