By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Special report needed

100+
P: 434
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.
Jan 1 '10 #1

✓ answered by 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Activate()
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Const conSPACE As String = vbCrLf & vbCrLf
    5. Dim strBuild_1 As String
    6. Dim strBuild_2 As String
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
    10.  
    11. With rst
    12.   Do While Not .EOF
    13.     If ![Line Number] Mod 2 = 0 Then
    14.       strBuild_1 = strBuild_1 & ![Name] & conSPACE
    15.       strBuild_2 = strBuild_2 & ![Line Number] & conSPACE
    16.     Else
    17.       strBuild_1 = strBuild_1 & ![Name] & vbCrLf
    18.       strBuild_2 = strBuild_2 & ![Line Number] & vbCrLf
    19.     End If
    20.       .MoveNext
    21.   Loop
    22.     Me![Text1] = strBuild_1
    23.     Me![Text2] = strBuild_2
    24. End With
    25.  
    26. rst.Close
    27. Set rst = Nothing
    28. End Sub

Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,597
report that will print two lines then leave a space then print another two lines.
Not exactly sure what you mean, CD Tom.
Jan 1 '10 #2

100+
P: 434
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
Jan 1 '10 #3

nico5038
Expert 2.5K+
P: 3,072
I would probably chose the "easy" way to manipulate the report's query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Test.ID, Test.Field1, [ID] &  "a" as Type
  2. FROM Test
  3. UNION 
  4. SELECT Test.ID,"", [ID] & "b"
  5. FROM Test where [ID] Mod 2 = 0 
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. ID    Field1    Type
  2. 1    Tom 1    1a
  3. 2    Bill 2    2a
  4. 2        2b
  5. 3    Sam 3    3a
  6. 4    Georeg 4    4a
  7. 4        4b
  8. 5    Tony 5    5a
  9. 6    Arthur 6    6a
  10. 6        6b
  11. 7    Jared 7    7a
  12. 8    Matt 8    8a
  13. 8        8b
Nic;o)
Jan 2 '10 #4

ADezii
Expert 5K+
P: 8,597
Hello Nico5038, what effect does the ORDER BY 3 have?
Jan 2 '10 #5

ADezii
Expert 5K+
P: 8,597
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Activate()
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Const conSPACE As String = vbCrLf & vbCrLf
    5. Dim strBuild_1 As String
    6. Dim strBuild_2 As String
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
    10.  
    11. With rst
    12.   Do While Not .EOF
    13.     If ![Line Number] Mod 2 = 0 Then
    14.       strBuild_1 = strBuild_1 & ![Name] & conSPACE
    15.       strBuild_2 = strBuild_2 & ![Line Number] & conSPACE
    16.     Else
    17.       strBuild_1 = strBuild_1 & ![Name] & vbCrLf
    18.       strBuild_2 = strBuild_2 & ![Line Number] & vbCrLf
    19.     End If
    20.       .MoveNext
    21.   Loop
    22.     Me![Text1] = strBuild_1
    23.     Me![Text2] = strBuild_2
    24. End With
    25.  
    26. rst.Close
    27. Set rst = Nothing
    28. End Sub
Jan 2 '10 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 2 '10 #7

100+
P: 434
Thanks you so much for the reply I'll try both and see which one works best for me. Thanks again.
Jan 2 '10 #8

ADezii
Expert 5K+
P: 8,597
Thanks Nico, you learn something new every day around here! (LOL)!
Jan 3 '10 #9

100+
P: 434
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.
Jan 3 '10 #10

ADezii
Expert 5K+
P: 8,597
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.
Jan 3 '10 #11

ADezii
Expert 5K+
P: 8,597
Hello CDTom, see if the Attachment works for you.
Attached Files
File Type: zip CDTom.zip (17.4 KB, 65 views)
Jan 3 '10 #12

100+
P: 434
That worked I thought I had set the can grow to yes but I must have forgotten. Thanks for all your help.
Jan 3 '10 #13

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Jan 3 '10 #14

nico5038
Expert 2.5K+
P: 3,072
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
Expand|Select|Wrap|Line Numbers
  1. SELECT Test.ID, Test.Field1,  "a" as Type
  2. FROM Test
  3. UNION 
  4. SELECT Test.ID,"", "b"
  5. FROM Test where [ID] Mod 2 = 0 
  6. order by 1, 3
Nic;o)
Jan 4 '10 #15

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 4 '10 #16

ADezii
Expert 5K+
P: 8,597
@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.
Jan 4 '10 #17

100+
P: 434
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.
Jan 4 '10 #18

NeoPa
Expert Mod 15k+
P: 31,186
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?
Jan 4 '10 #19

100+
P: 434
Yes it does make sense, I've been playing around with it and it does seem easier. Thanks for everything.,
Jan 4 '10 #20

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 4 '10 #21

Post your reply

Sign in to post your reply or Sign up for a free account.