Hi all, still working on my EMR and you guys were super helpful at getting close to an answer before. Right now I have this complex arrangement that involves

• picking dates off of a form,
• using the dates to query a datasheet and a subdatasheet connected by a LEFTJOIN on the PK using the Between...And... Function
• and produce a subreport on a larger report.

However, the query won't run. Everytime I click run for any date range that may have data in it I get this:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"

What's weird is that these same functions work for my other subreport/query that is not connected by a JOIN to another table.

Here's the generated SQL:

SELECT [Monthly Reports Table].[Date of Reference Month/Year], [Monthly Reports Table].[YSSP or SBSP], Sum([Monthly Reports Table].[# of Males Screened]) AS [SumOf# of Males Screened], Sum([Monthly Reports Table].[# of Females Screened]) AS [SumOf# of Females Screened], Sum([Monthly Reports Table].[# of Transgender persons Screened]) AS [SumOf# of Transgender persons Screened], Sum([Monthly Reports Table].[# of Agender persons Screened]) AS [SumOf# of Agender persons Screened], Sum([Monthly Reports Table].[13 year olds Screened]) AS [SumOf13 year olds Screened], Sum([Monthly Reports Table].[14 Year olds Screened]) AS [SumOf14 Year olds Screened], Sum([Monthly Reports Table].[15 Year Olds Screened]) AS [SumOf15 Year Olds Screened], Sum([Monthly Reports Table].[16 Year Olds Screened]) AS [SumOf16 Year Olds Screened], Sum([Monthly Reports Table].[17 Year Olds Screened]) AS [SumOf17 Year Olds Screened], Sum([Monthly Reports Table].[18 Year Olds Screened]) AS [SumOf18 Year Olds Screened], Sum([Monthly Reports Table].[19 Year Olds Screened]) AS [SumOf19 Year Olds Screened], Sum([Monthly Reports Table].[20 Year Olds Screened]) AS [SumOf20 Year Olds Screened], Sum([Monthly Reports Table].[21 Year Olds Screened]) AS [SumOf21 Year Olds Screened], Sum([Monthly Reports Table].[22 Year Olds Screened]) AS [SumOf22 Year Olds Screened], Sum([Monthly Reports Table].[23 Year Olds Screened]) AS [SumOf23 Year Olds Screened], Sum([Monthly Reports Table].[24 Year Olds Screened]) AS [SumOf24 Year Olds Screened], Sum([Monthly Reports Table].[24 Screened]) AS [SumOf24 Screened], [SBSP Monthly Report Table].[Is SBSP?], Sum([SBSP Monthly Report Table].[# of DCPS High School]) AS [SumOf# of DCPS High School], Sum([SBSP Monthly Report Table].[# of Public Charter School]) AS [SumOf# of Public Charter School], Sum([SBSP Monthly Report Table].[# of Alternative School]) AS [SumOf# of Alternative School], Sum([SBSP Monthly Report Table].[# of Participants]) AS [SumOf# of Participants], Sum([SBSP Monthly Report Table].[Total # of Schools Served]) AS [SumOfTotal # of Schools Served], Avg([SBSP Monthly Report Table].[Testing Percentage]) AS [AvgOfTesting Percentage]

FROM [Monthly Reports Table] LEFT JOIN [SBSP Monthly Report Table] ON ([Monthly Reports Table].[YSSP or SBSP] = [SBSP Monthly Report Table].[Is SBSP?]) AND ([Monthly Reports Table].[Entry ID] = [SBSP Monthly Report Table].[Entry ID])

GROUP BY [Monthly Reports Table].[Date of Reference Month/Year], [Monthly Reports Table].[YSSP or SBSP], [SBSP Monthly Report Table].[Is SBSP?]

HAVING ((([Monthly Reports Table].[Date of Reference Month/Year]) Between [Forms]![Create Final Report]![StartDateQuery] And [Forms]![Create Final Report]![EndDateQuery]) AND (([Monthly Reports Table].[YSSP or SBSP])="SBSP") AND (([SBSP Monthly Report Table].[Is SBSP?])="SBSP"));

Things to note, I don't know how to Define the Parameters in the Parameters Dialog box because the Parameters are functions (specifically e.g. Forms!Create Final Report!StartDateQuery); I also don't know how to make my function parameter into a set of variables that could possibly simplify this.