Access 2007 Error 3071 -report using parameters

Question

I have a query in MS Access 2007 that pulls data from two different tables and displays in a report. This occurs after a user clicks a button on the main form, which opens a date field parameter form where the user can select two dates. From there the query runs using the two dates the user provided, or the dates are 'faked' if none are selected to fill in the dates. I'm getting a Run-time error 3071, this expression is typed incorrectly, or it is too complex to be evaluated and i'm not sure why.

I run a similar query in another database and it executes perfectly so i'm at a loss.

Query is below,

(SELECT 
[Group Name], 
tbGroups AS [Group Number],
Analyst, 
[Account Manager], 
NULL AS [SER Number], 
[Received Date] AS [Corporate Recevied],
DateValue(Created) AS [Sales Submitted], 
tbBAAcceptedDate AS [BA Accepted], 
NULL AS [Submitted to MDSS], 
NULL AS [Completed],
NULL AS [Cancelled],
DateDiff("d",[Received Date], IIf([Forms]![frmReportDateFilter].[tbToDate] = '01/01/2116', Date(), CDate([Forms]![frmReportDateFilter].[tbToDate]))) AS [Aging Days Count],
LocalID AS [ID Number]
FROM ChangeRequest
WHERE DateValue([Created]) BETWEEN CDate([Forms]![frmReportDateFilter].[tbFromDate]) AND CDate([Forms]![frmReportDateFilter].[tbToDate]))
UNION ALL (SELECT 
tbGroupProgramProductName AS [Group Name], 
tbGroups AS [Group Number], 
cboAnalyst AS Analyst, 
tbAccountManager AS [Account Manager], 
tbSERNumber AS [SER Number], 
tbCorpReceivedDate AS [Corporate Recevied],
tbBAReceivedDate AS [Sales Submitted],
IIf([tbBAAcceptedDate] > CDate([Forms]![frmReportDateFilter].[tbToDate]), NULL, [tbBAAcceptedDate]) AS [BA Accepted],
IIf([tbSubmittedToMDSS] > CDate([Forms]![frmReportDateFilter].[tbToDate]), NULL, [tbSubmittedToMDSS]) AS [Submitted to MDSS],
DateValue(tbCompleteDate) AS [Completed],
DateValue(tbCancelDate) AS [Cancelled],
DateDiff("d",tbCorpReceivedDate, IIf([Forms]![frmReportDateFilter].[tbToDate] = '01/01/2116', Date(), CDate([Forms]![frmReportDateFilter].[tbToDate]))) AS [Aging Days Count],
LocalID AS [ID Number]
FROM tblDD
WHERE DateValue([tbBAReceivedDate]) BETWEEN CDate([Forms]![frmReportDateFilter].[tbFromDate]) AND CDate([Forms]![frmReportDateFilter].[tbToDate]))
ORDER BY [Group Name];

Any help is greatly appreciated.

What happens if you eliminate the parentheses around each SELECT piece? In other words, SELECT ... FROM ... UNION ALL SELECT ... FROM ... instead of (SELECT ... FROM ...) UNION ALL (SELECT ... FROM ...) — HansUp
@Hans - I thought of that as well and when i remove them I get the same error, if memory serves me correct that was causing a different error in the previous query I spoke of that is executing correctly. I think i'm missing or overlooking something in the code as I cannot get rid of this run-time error.. — Shaun K
Do you get that same error when testing the query by itself in the query designer? If so, and you break out and test each SELECT piece separately, do those both run without error? — HansUp
Breaking out the two SELECT pieces separately led me to find that my top SELECT is functioning but my bottom, not so much. Same error when I run only the bottom half of the query. I didn't not build in the designer I wrote out the SQL. — Shaun K
Good! Now determine which part of that query causes the error. For example, do you still get the error if you SELECT *? — HansUp

Gustav Gustav · Accepted Answer · 2015-12-07T17:55:59

Specify the control as Date. Something like this with no CDate - and no outer parenthesis:

PARAMETERS 
[Forms]![frmReportDateFilter].[tbFromDate] Date,
[Forms]![frmReportDateFilter].[tbToDate] Date;
SELECT 
[Group Name], 
tbGroups AS [Group Number],
Analyst, 
[Account Manager], 
NULL AS [SER Number], 
[Received Date] AS [Corporate Recevied],
DateValue(Created) AS [Sales Submitted], 
tbBAAcceptedDate AS [BA Accepted], 
NULL AS [Submitted to MDSS], 
NULL AS [Completed],
NULL AS [Cancelled],
DateDiff("d",[Received Date], IIf([Forms]![frmReportDateFilter].[tbToDate] = #01/01/2116#, Date(), [Forms]![frmReportDateFilter].[tbToDate])) AS [Aging Days Count],
LocalID AS [ID Number]
FROM ChangeRequest
WHERE DateValue([Created]) BETWEEN [Forms]![frmReportDateFilter].[tbFromDate] AND [Forms]![frmReportDateFilter].[tbToDate]
UNION ALL 
SELECT 
tbGroupProgramProductName AS [Group Name], 
tbGroups AS [Group Number], 
cboAnalyst AS Analyst, 
tbAccountManager AS [Account Manager], 
tbSERNumber AS [SER Number], 
tbCorpReceivedDate AS [Corporate Recevied],
tbBAReceivedDate AS [Sales Submitted],
IIf([tbBAAcceptedDate] > [Forms]![frmReportDateFilter].[tbToDate], NULL, [tbBAAcceptedDate]) AS [BA Accepted],
IIf([tbSubmittedToMDSS] > [Forms]![frmReportDateFilter].[tbToDate], NULL, [tbSubmittedToMDSS]) AS [Submitted to MDSS],
DateValue(tbCompleteDate) AS [Completed],
DateValue(tbCancelDate) AS [Cancelled],
DateDiff("d",tbCorpReceivedDate, IIf([Forms]![frmReportDateFilter].[tbToDate] = #01/01/2116#, Date(), [Forms]![frmReportDateFilter].[tbToDate])) AS [Aging Days Count],
LocalID AS [ID Number]
FROM tblDD
WHERE DateValue([tbBAReceivedDate]) BETWEEN [Forms]![frmReportDateFilter].[tbFromDate] AND [Forms]![frmReportDateFilter].[tbToDate]
ORDER BY [Group Name];

Access 2007 Error 3071 -report using parameters

1 Answers