Adding blank rows to a report to fill a page

KitaYama

Well-known member
Local time
Tomorrow, 01:30
Joined
Jan 6, 2022
Messages
1,951
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!
Code:
=IIf(Nz([ReqUnitPrice],0)>0,[ReqQuantity]*([ReqUnitPrice]+Nz([OtherExpenses],0)),"")

2022-09-29_14-42-16.png

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:

2022-09-29_15-04-15.png


How can I modify this sql or the calculated field to prevent #Type! error.

Thank you.
 
Last edited:
Just to add one point I forgot to explain.

If I add 0,0,0..... instead of '', '', '', to union sql #Type! doesn't show. But the report is filled with 0s.

I can't use conditional Formatting to hide 0s because the table may have some 0s in some fields and they have to be shown.


Thank you again.
 
you are Indexing on Column 1 and 2.
so on your Dummy you add 99999999, 99999999 as your first
2 columns.
this will bring your dummy records to the bottom.
you can then use CF, using "Expression"

[FieldName] >= 99999999
 
you are Indexing on Column 1 and 2.
so on your Dummy you add 99999999, 99999999 as your first
2 columns.
this will bring your dummy records to the bottom.
you can then use CF, using "Expression"

[FieldName] >= 99999999
I will test and be back.

Thanks.
 
@arnelgp

I added a database to my first post above for testing.
I may have misunderstood you, but it's still the same.
Did you mean to use this sql ?

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,
    99999999, 99999999, '', '', '', '', '', '', '', '', '', '', ''
FROM
    tblReportDummy
Order By
    1, 2

Thank you
 
Last edited:
The blank rows show #Type!
Try that 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, 99999999, 99999999, 
    '', '', '', '', '', '', 0 as ReqQuantity, '', '', 
    Null as ReqUnitPrice, 0 as OtherExpenses
FROM tblReportDummy 
ORDER BY 1, 2;
 
Try that 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, 99999999, 99999999,
    '', '', '', '', '', '', 0 as ReqQuantity, '', '',
    Null as ReqUnitPrice, 0 as OtherExpenses
FROM tblReportDummy
ORDER BY 1, 2;
Thanks for your try. But your query shows 0 in quantity field for all blank rows.
As I explained in my second post I can't use conditional formatting to hid 0 because at times it MAY be 0.

Thanks again.

2022-09-29_17-49-41.png
 
You could use CF based on the other fields being blank and the value is 0?
 
i thought you got the idea i gave you in #3.
i meant "Expression", therefore you are comparing not
the current field?
i did not see your uploaded db.
here i made the records in tblReportDummy "very" large.
see the Recordsource of the Report.
 

Attachments

Though the problem is solved I think I need a little more help here.
I can't understand why I was receiving #Type! error in first place.

I have an IIf statement in total textbox
Code:
=IIf(Nz([ReqUnitPrice],0)>0,[ReqQuantity]*([ReqUnitPrice]+Nz([OtherExpenses],0)),"")

IIf(Nz([ReqUnitPrice],0)>0

Since this returns False, record source of the textbox would be ""

Why the text box shows the #Type! error.

And why putting 0 in quantity solves this?

thanks.
 
i thought you got the idea i gave you in #3.
i meant "Expression", therefore you are comparing not
the current field?
i did not see your uploaded db.
here i made the records in tblReportDummy "very" large.
see the Recordsource of the Report.
@arnelgp
thanks for your help. And sorry for misunderstanding you.
Unfortunately I don't have Access at home.
I will check your solution tomorrow morning.

Thank you
 

Users who are viewing this thread

Back
Top Bottom