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]

Special Report

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 programmaticall y, 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]

The ORDER BY clause allows to use the column (sequence) number instead of the column name.

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)