How to make a blank row in a query (1 Viewer)

[MatMac]

Hi. Can anyone tell me the easiest way to may a query return a single blank row - ONLY if the query would otherwise not return any results.

This is will me with formatting of a report based on the query.

Thanks - Mat.

 

[ajetrumpet]

you can do this with a complicated VBA function, but if there aren't too many columns in your query, just use the zero length string, sort of like this:

SELECT DISTINCT "", "", "", "", "", "" FROM table

You may have to use a DCOUNT() function to count the records in the query first though...


the other alternative would be to make the query above as a seperate query altogether, and then just make a report off of it, and insert that stuff manually into your report when you need an extra line? but that's probably too cumbersome to figure out or do.

 

[Dennisk]

why not use a union query to always append a blank recordset to your main query.

 

[MatMac]

Thanks guys. The above ideas do add a blank row, but the problem is to add a blank row ONLY when the query returns nothing else.

The reason for this is that - in some reports - it is neccessary to have something in place within a subreport, to preserve the overall report formatting. Otherwise, if the subreport for the query returns nothing, then a space in the main report exists. However, if a blank row is always appended, then this can look wrong in the main report.

Anyway, I have finally worked out how to do it, so post my solution here in case its of help to anyone else.

1) Run a create table query to produce a temp table, on which to base your query.

2) Use an If statement to append a blank row to this table if you criteria from the original query are not met in any cases. (I.e. if you query doesn't return any results.)

3) Amend you query to base it on the Temp table rather than the original table.

4) Amend your query to return rows according to your original criteria OR some criterion which returns you appended blank row.

 

[boblarson]

That solution sounds harder than having a query that returns a blank record always and then in the Report's On Open event (or On Load if 2007), you test to see if the normal query has records and if not, just change the report's recordsource to be the blank query.

 

[MatMac]

Hi Bob - indeed your solution seems a lot more elegant - so thanks for that.

However, it does touch on an issue I have as mentioned in another active thread. Basically, these are subreports and when opened from the parent report, the On Activate event is not triggered (problem with the On Open event as well))...

http://www.access-programmers.co.uk/forums/showthread.php?t=169272

Mat.

 

[Poppa Smurf]

Could you use the NoData event for each subreport and have a label display "NO data" on the subreport when there is no data for the sub report?

 

[MatMac]

Sadly not that simple. My report has to look like a completed form, and must include tables which - if no data - look like they are simply not filled in. My subreports hence "build" sections of these tables so must return at least one blank row. I am strictly constrained by what my output must look like.

...buy - hey - I have got it working - as mentioned above