Special report needed

[CD Tom]

I need a report that will print two lines then leave a space then print another two lines. The file I'm using has a line number field that can run from 1 - 8 or 1 - 14 depending on the what the user chooses. The way the report needs to look is line 1 and 2 are together then a space then 3 and 4 go together and another space. Not to sure how to get this to work or if its possible. Any help will be greatly appreciated.

[ADezii]

report that will print two lines then leave a space then print another two lines.

Not exactly sure what you mean, CD Tom.

[CD Tom]

Hopefully I can expain it better.
I have a file that has Name and Line number the line number can be 1-8 or 1-14 depending on the users selection
I want a report to look like this
Name Line Number
Tom 1
Bill 2
(blank Line)
Sam 3
Georeg 4
(Blank Line)
Tony 5
Arthur 6
(Blank Line)
Jared 7
Matt 8
(blank Line)
William 1
Greg 2
etc.

I hope this makes some sense.

Thanks Tom

[nico5038]

I would probably chose the "easy" way to manipulate the report's query like:

SELECT Test.ID, Test.Field1, [ID] &  "a" as Type
FROM Test
UNION 
SELECT Test.ID,"", [ID] & "b"
FROM Test where [ID] Mod 2 = 0 
order by 3

Thus an additional record is created for every "even" line.
I used the sample names in a table with an ID and got:

ID    Field1    Type
1    Tom 1    1a
2    Bill 2    2a
2        2b
3    Sam 3    3a
4    Georeg 4    4a
4        4b
5    Tony 5    5a
6    Arthur 6    6a
6        6b
7    Jared 7    7a
8    Matt 8    8a
8        8b

Nic;o)

[ADezii]

Hello Nico5038, what effect does the ORDER BY 3 have?

[ADezii]

Besides Nico's approach, what you are requesting can be accomplished programmatically, although in a round-a-bout way:

1. Create an 'Unbound' Report.
2. Create 2 'Unbound' Text Boxes on the Report (Text1, Text2) and position them on the Report exactly where you wish the values to appear.
3. Set their Can Grow Properties to Yes.
4. Execute the following code in the Report's Activate() Event.
5. I assumed your Table Name is Table1.
   

   Private Sub Report_Activate()
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Const conSPACE As String = vbCrLf & vbCrLf
   Dim strBuild_1 As String
   Dim strBuild_2 As String
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
    
   With rst
     Do While Not .EOF
       If ![Line Number] Mod 2 = 0 Then
         strBuild_1 = strBuild_1 & ![Name] & conSPACE
         strBuild_2 = strBuild_2 & ![Line Number] & conSPACE
       Else
         strBuild_1 = strBuild_1 & ![Name] & vbCrLf
         strBuild_2 = strBuild_2 & ![Line Number] & vbCrLf
       End If
         .MoveNext
     Loop
       Me![Text1] = strBuild_1
       Me![Text2] = strBuild_2
   End With
    
   rst.Close
   Set rst = Nothing
   End Sub

[nico5038]

Hi ADezii,

The ORDER BY clause allows to use the column (sequence) number instead of the column name.
So when the column is an algorithm I prefer to use the number instead of repeating the algorithm.

Nic;o)

[CD Tom]

Thanks you so much for the reply I'll try both and see which one works best for me. Thanks again.

[ADezii]

Thanks Nico, you learn something new every day around here! (LOL)!

[CD Tom]

Adezii
I've been trying your approach but I only get one line to print. Am I missing something. I have the text1 and text2 fields in the detail section of the report, I also tried putting them in the page header section. Thanks for your help I hope I can get this to work.

[ADezii]

I'll get a Demo DB together sometime today and make it available for you as an Attachment. Cold very possibly be that I misinterpreted something.

[ADezii]

Hello CDTom, see if the Attachment works for you.

[CD Tom]

That worked I thought I had set the can grow to yes but I must have forgotten. Thanks for all your help.

[NeoPa]

@Nico5038
Trust you to know something like that Nico. I searched Help and it wasn't there. I also tested it (just to be sure. You know I expected it to work as you'd said it does.) and it works of course. This is a bit of a revelation for me. Thanks for the info.

[nico5038]

Glad to have added something to your knowledge :-)

The query is however not 100 % as the sort will be alphabetical, thus the 1A will be followed by 10A, etc.

The correct query is

SELECT Test.ID, Test.Field1,  "a" as Type
FROM Test
UNION 
SELECT Test.ID,"", "b"
FROM Test where [ID] Mod 2 = 0 
order by 1, 3

Nic;o)

[NeoPa]

Indeed. Clearly a tidier solution :)

I suppose there is no easy way of writing it in such a way as not to be dependent on the ID being in perfect sequence? I know ordinals in SQL are often sticky & I certainly see no obvious alternative.

[ADezii]

@CD Tom
Hello CD Tom. Just for curiosity, have you tried Nico's approach, the final version of which is displayed in Post #15? I think that Nico's approach is more efficient, cleaner, and adaptable.

[CD Tom]

I haven't tried it Nico's approach, I guess I'm a little confused by it, not knowing for sure hope it works. I'll test it and see if it will become clear.

[NeoPa]

In Nico's solution he uses an [ID] field to determine if the record is an even line or an odd one. This is fine if that matches your table. Otherwise, use whatever you have that displays this characteristic. From what you posted earlier I'm guessing you may have a [Line Number] field that may be appropriate. What is important for it to work is that you use a field that is even for each line before the extra (blank) line that you want added, but odd for all the ones after it. Does that make sense?

[CD Tom]

Yes it does make sense, I've been playing around with it and it does seem easier. Thanks for everything.,

[nico5038]

Not only easier, but also faster :-)
VBA seems to slow things down in general at a 1:6 ratio or even worse...

So I prefer in general SQL (read query) solutions to have the database engine do the work.

Don't forget to use the second query I posted (With two sort fields), as that's working for every number of lines !

Nic;o)