Adding blank rows to a report to fill a page

[KitaYama]

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!

=IIf(Nz([ReqUnitPrice],0)>0,[ReqQuantity]*([ReqUnitPrice]+Nz([OtherExpenses],0)),"")

This is the Union query:

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.

 

[KitaYama]

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.

 

[arnelgp]

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

 

[KitaYama]

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.

 

[KitaYama]

@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 ?

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

 

[Eugene-LS]

The blank rows show #Type!

Try that query:

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;

 

[KitaYama]

Try that query:

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.

 

[Minty]

You could use CF based on the other fields being blank and the value is 0?

 

[KitaYama]

You could use CF based on the other fields being blank and the value is 0?

Thanks for the idea.
I'll check if I can make it work.

 

[KitaYama]

@Minty You're a genius. I never thought of that.
Excellent
And Million thanks.

 

[arnelgp]

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.

 

[KitaYama]

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

=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.

 

[KitaYama]

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