source Query works, Chart Doesnt

[cmartin1986]

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.

SELECT Format([received date],"mmmmyyyy") AS [date], [tbl-tag_master].Supplier, Sum(1) AS total
FROM [tbl-tag_master]
WHERE ((([tbl-tag_master].[Received Date]) Between [Forms]![Front]![Combo8] And [forms]![front]![combo10]))
GROUP BY Format([received date],"mmmmyyyy"), [tbl-tag_master].Supplier;
 

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

[Stewart Ross]

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.

SELECT Format([received date],"mmmmyyyy") AS [date], [tbl-tag_master].Supplier, Sum(1) AS total
FROM [tbl-tag_master]
WHERE ((([tbl-tag_master].[Received Date]) Between [Forms]![Front]![Combo8] And [forms]![front]![combo10]))
GROUP BY Format([received date],"mmmmyyyy"), [tbl-tag_master].Supplier;
 

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.

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

Then replace the references to the form fields in your query criteria cells with calls to the function above, like this:

Between FormFieldValue("Front", "combo8") And FormFieldValue("front", "combo10")

-Stewart

[cmartin1986]

Thanks a million, worked perfect!

[Stewart Ross]

Thanks a million, worked perfect!

Glad to have been able to help, Chad! -S