I use a function to create a SQL for my reports. The function receives a report name and a filter, calculates the result and create a union sql and sets it as report's record source to have the blank rows.
Everything's perfect except when I have a calculated field in report.
In bellow screen shot, the field in red square is a calculated field in a report. The blank rows show #Type!
This is the Union query:
This is tblReportDummy's structure:
How can I modify this sql or the calculated field to prevent #Type! error.
Thank you.
Everything's perfect except when I have a calculated field in report.
In bellow screen shot, the field in red square is a calculated field in a report. The blank rows show #Type!
Code:
=IIf(Nz([ReqUnitPrice],0)>0,[ReqQuantity]*([ReqUnitPrice]+Nz([OtherExpenses],0)),"")
This is the Union query:
SQL:
SELECT
0 As Expr1,
EstimatePK, OrderedPartsPK, ReqRecID, ReqOrderedPart,
ReqOrderedPartChanges, ReqOrderedPartName, RegisteredOn,
ReqSupplierFK, ReqQuantity, ReqUnitFK, ReqRemarks, ReqUnitPrice, OtherExpenses
FROM
tblEstimate
WHERE
ReqOrderedPart LIKE '*AF3602BE602*'
Union All
SELECT Top 4
Expr1 AS EstimatePK,
'', '', '', '', '', '', '', '', '', '', '', '', ''
FROM
tblReportDummy
Order By
1, 2
This is tblReportDummy's structure:
How can I modify this sql or the calculated field to prevent #Type! error.
Thank you.
Last edited: