Insert Blank Line In Query?

[MDW]

Not sure if this is possible. I've got a query that returns records like this
STORE_NUM NAME DIV_CODE
1 Main St. OA
2 Broadway OA
5 Sicily OA
6 Xenia OB
8 Trenton Av. OB
9 Platsville OC

Is there a way I can have a blank row in the query at each change in the
DIV_CODE? So it'd return this:
STORE_NUM NAME DIV_CODE
1 Main St. OA
2 Broadway OA
5 Sicily OA

6 Xenia OB
8 Trenton Av. OB

9 Platsville OC

Thanks.

 

[Guest]

Maybe the simplest thing to do is create a bare-bones
report based on the query and grouped by DIV_CODE. If all
you want is a simple list, you can turn off the group
header and simply open the footer up a linespace high.

Just a thought.

 

[MDW]

Unfortunately, this isn't going into a report. It's going to be exported to
Excel, where currently I have to go into the workbook and manually enter
those blank lines (rows) myself. Was hoping Access had some way to do it for
me.

Thanks though.

Maybe the simplest thing to do is create a bare-bones
report based on the query and grouped by DIV_CODE. If all
you want is a simple list, you can turn off the group
header and simply open the footer up a linespace high.

Just a thought.

 

[Tom Ellison]

Dear MDW:

This is hardly what queries are designed to do, and you'd be much
better off creating a report, which allows for something like this.

However, you can create another query that says:

SELECT DISTINCT DIV_CODE
FROM YourOtherQuery

Create a UNION ALL of your existing query with the above, adding the
columns STORE_NUM and NAME with blank values in them. Also add a new
column to both queries I'll call STORE_ORDER. In the existing query,
make STORE_ORDER to be the same values as STORE_NUM. In the new
query's portion of the UNION ALL, put a value like 999 in for
STORE_ORDER (or some other impossibly high value) so the new "BLANK
LINE" will appear at the end of the list for each DIV_CODE when the
whole thing is sorted by DIV_CODE. If you don't want to show the
DIV_CODE in the "BLANK LINE" then make yet another DIV_ORDER column
for sorting and leave DIV_CODE blank.

With some ingenuity, you can get queries to do stuff like this.
Still, I recommend against it. Reports are a much better way to do
this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[MDW]

I don't want to completely bastardize the query. If you read my response to
anonymous, this isn't going into a report but rather to Excel, where those
blank lines are manually added.

I guess I'll see about writing a macro in Excel that can do this.

Thanks anyway.

 

[Guest]

Have experimented with this after your reply. You can
export a report to Excel. If you make the bare-bones
report I suggested and leave an open Group HEADER (not
footer), the exported report will have a blank line
between groups. You will have to remove the leading blank
line (first header) and perhaps the column label, but you
get your blank lines between groups.

Let me know if this works for you.

-----Original Message-----
Unfortunately, this isn't going into a report. It's going to be exported to
Excel, where currently I have to go into the workbook and manually enter
those blank lines (rows) myself. Was hoping Access had some way to do it for
me.

Thanks though.