"This expression is typed incorrectly, or it is too...

[amauricio2]

Can someone tell me what is wrong with my query

SELECT MTCQ.MONTH, MTCQ.YEAR, MTCQ.DISTRIBUTOR, MTCQ.UPC, MTCQ.[ITEM
DESCRIPTION], MTCQ.SIZE, MTCQ.[CASE PACK], MTCQ.[DIRECT IMPORT], MTCQ.[BDM
STATUS], Sum(MTCQ.tncp) AS SumOftncp, Sum([Tbl_Cumm Actual Results].[BILL
UNITS]) AS [SumOfBILL UNITS], Sum(([BILL UNITS]/[CASE PACK])) AS [BILL CASES],
Sum([Tbl_Cumm Actual Results].[SALES UNITS]) AS [SumOfSALES UNITS], Sum((
[SALES UNITS]/[CASE PACK])) AS [SALES CASES]
FROM MTCQ INNER JOIN [Tbl_Cumm Actual Results] ON (MTCQ.UPC = [Tbl_Cumm
Actual Results].UPC) AND (MTCQ.[CORPORATE NUMBER] = [Tbl_Cumm Actual Results].
[CORPORATE NUMBER]) AND (MTCQ.[START DATE] = [Tbl_Cumm Actual Results].[START
DATE])
GROUP BY MTCQ.MONTH, MTCQ.YEAR, MTCQ.DISTRIBUTOR, MTCQ.UPC, MTCQ.[ITEM
DESCRIPTION], MTCQ.SIZE, MTCQ.[CASE PACK], MTCQ.[DIRECT IMPORT], MTCQ.[BDM
STATUS]
HAVING (((MTCQ.MONTH)=[What Month?]) AND ((MTCQ.YEAR)=[What Year?]) AND (
(MTCQ.[BDM STATUS])="APPROVED"));

This query will work for Jan through July then give me the "This expression
is typed incorrectly, or it is too complex to be evaluated" error. I really
appreciate your help folks!!

 

[Allen Browne]

Suggestions:

1. Declare your parameters.
In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows, like this:
[What Month?] Long
[What Year?] Long
This assumes that your Month and Year fields are type Number if you open the
MTCQ table in design view.

2. Add square brackets around the MONTH and YEAR fields.
You will probably be okay in this context, but in a form or report, Access
may confuse these names with the functions of the same name.

If that does not solve it, there must be other cases where the data type is
indeterminate, inappropriate, or not correctly recognised. You might also
check for records where [CAST PACK] is zero, as these would generate a
division-by-zero error.

 

[amauricio2]

Genius absolutely, absolutely genius. You were right it was the [Case Rack]

Suggestions:

1. Declare your parameters.
In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows, like this:
[What Month?] Long
[What Year?] Long
This assumes that your Month and Year fields are type Number if you open the
MTCQ table in design view.

2. Add square brackets around the MONTH and YEAR fields.
You will probably be okay in this context, but in a form or report, Access
may confuse these names with the functions of the same name.

If that does not solve it, there must be other cases where the data type is
indeterminate, inappropriate, or not correctly recognised. You might also
check for records where [CAST PACK] is zero, as these would generate a
division-by-zero error.

Can someone tell me what is wrong with my query

[quoted text clipped - 22 lines]

really
appreciate your help folks!!