423,850 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

Query in Microsoft Access 2013

P: 13
I need to add a phrase into the following SQL that will performed the following: If the sum of [VT] reaches 20 insert a line or some type of divider at that point. I essentially need the report to divide VT into sets of 1-20, 21-40 etc....

TRANSFORM Count([Weld Log].Activity) AS CountOfActivity
SELECT [Weld Log].[Welder/Insp], [Weld Log].[Spool #], [Weld Log].[Weld #], [Weld Log].WPS, [Weld Log].Type, [Weld Log].Date, [Weld Log].[Pass or Fail], [Weld Log].Comment, Count([Weld Log].Activity) AS [Total Of Activity]
FROM [Weld Log]
WHERE ((([Weld Log].Type)="BUTTWELD") AND ((([Weld Log].[Pass or Fail])="PASS" Or ([Weld Log].[Pass or Fail])="Fail") And (([Weld Log].[Pass or Fail])="Pass" Or ([Weld Log].[Pass or Fail])="Fail")))
GROUP BY [Weld Log].[Welder/Insp], [Weld Log].[Spool #], [Weld Log].[Weld #], [Weld Log].WPS, [Weld Log].NDE, [Weld Log].Type, [Weld Log].Date, [Weld Log].[Pass or Fail], [Weld Log].Comment
PIVOT [Weld Log].NDE;

I have attached a copy of the crosstab Query and Report. The report is perfect except for the fact that under the detail portion I need to divide at sum of 20. There will be a separate report for each welder stencil.
Attached Files
File Type: doc Crosstab Query.doc (40.0 KB, 10 views)
File Type: pdf Report.pdf (746.2 KB, 11 views)
Oct 3 '18 #1

✓ answered by PhilOfWalton

Welcome to Bytes

I suggest you leave your query alone. What you need to do is insert a page break after VT gets to 20

Try
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim VTCounter As Long
  5.  
  6. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  7.  
  8.     VTCounter = VTCounter + Nz(VT)
  9.     If VTCounter = 20 Then
  10.         Detail.ForceNewPage = 2   ' after section
  11.         VTCounter = 0
  12.     Else
  13.         Detail.ForceNewPage = 0   ' none
  14.     End If
  15.  
  16. End Sub
So we add up the VTs, when it gets to 20, force a new page and set the VTCounter back to 0.
Note VTCounter is in the header of the module

Phil

Share this Question
Share on Google+
19 Replies


PhilOfWalton
Expert 100+
P: 1,376
Welcome to Bytes

I suggest you leave your query alone. What you need to do is insert a page break after VT gets to 20

Try
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim VTCounter As Long
  5.  
  6. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  7.  
  8.     VTCounter = VTCounter + Nz(VT)
  9.     If VTCounter = 20 Then
  10.         Detail.ForceNewPage = 2   ' after section
  11.         VTCounter = 0
  12.     Else
  13.         Detail.ForceNewPage = 0   ' none
  14.     End If
  15.  
  16. End Sub
So we add up the VTs, when it gets to 20, force a new page and set the VTCounter back to 0.
Note VTCounter is in the header of the module

Phil
Oct 3 '18 #2

P: 13
Do I just add this to the bottom of my SQL? Sorry just learning how Access works.
Oct 3 '18 #3

P: 13
I need to create a VBA. Just took me a moment to figure it out. Thanks!
Oct 3 '18 #4

PhilOfWalton
Expert 100+
P: 1,376
No, as I said nothing to do with your SQL

On your report, in design View, click on the bar that says "Detail"
Then find the Event tab on the property sheet.
On the line named On Format, click on the 3 dots ... on the right of the row. That should bring up a box with 3 options. Choose "Code Builder"

This will probably bring up some code - delete it and copy and paste the code I gave you in Post #2

Phil
Oct 3 '18 #5

P: 13
I followed the steps in your last response but it doesn't seem to be working. I have attached a screen shot of the report in design view and the code display.
Attached Files
File Type: doc Code Screen.doc (223.0 KB, 8 views)
File Type: doc Design view of Report.doc (177.5 KB, 7 views)
Oct 4 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,705
You may have to change line 8 to:

Expand|Select|Wrap|Line Numbers
  1. VTCounter = VTCounter + Nz(Me.VT, 0)
Oct 4 '18 #7

P: 13
I changed the to the above and I am getting page breaks every where in my report. When I copy the code into the display do I leave the box at the top selected as "detail" after I paste the code?
Oct 4 '18 #8

PhilOfWalton
Expert 100+
P: 1,376
I think Twinnyfo's suggestion is correct.

There is a lot of unnecessary white space on the right hand side of your report. If the report width is greater than the paper width, you may well get extra pages.
On your "Arrange" Tab, select "Size / Space" and select Ruler on, Grid on & snap to Grid.

Depending on whether you use Metric or Imperial measurements, on a Form or Report's format tab, I set both Grid X and Grid Y to either 5mm or 1/4". That makes controls easy to size and line up.

So first drag the right margin of the report as far to the left as it will go (it will jamb up against the Date & Time controls at the top of the form, which could be narrowed anyway).

Then we need to check the width of your report from the Ruler, and ensure that it is less than the width of the paper - left & right margin.

See if that cures the problem

Phil
Oct 4 '18 #9

P: 13
I removed the blank space and this is what the report looks like when I print it. I am not seeing where it is breaking on the section that the VT is over 20. Sorry to be such a pain I am pretty new at formatting reports.
Attached Files
File Type: pdf 20181004111000964.pdf (1.54 MB, 8 views)
Oct 4 '18 #10

PhilOfWalton
Expert 100+
P: 1,376
I'm having difficulty is understanding what is going wrong. I suspect it is a combination of the report putting in it's natural page breaks, and the code forcing additional page breaks.

I am going to suggest an approach which may be much clearer, but I leave you to judge.

There is an icon on your ribbon in the picture that says "Group & Sort". Click on that, and you will see information at the bottom of the screen.

Delete the rows that say Group on Spool #, Group on Weld # & Group on VT. You are not using them.

On the Group on Type, there are various options which you appear to have set correctly, but select the option "Keep whole group together on one page"

Unless the requirement for exactly 20 VT's per page is set in gold, I think this will make a much better report.

Phil
Oct 4 '18 #11

P: 13
Ok I started from scratch with my report. I thought maybe it was something in the settings I had changed that was causing the problem. However, I ran another one and I am still having problems. I attached the new report so you could take a look.
Attached Files
File Type: pdf 20181004144751425.pdf (891.4 KB, 5 views)
Oct 4 '18 #12

PhilOfWalton
Expert 100+
P: 1,376
Have you removed the Detail OnFormat Event?

Let's have another look at the report in Design view, showing the Group on bit at the bottom, and the Event tab for the Detail Section.

Phil
Oct 4 '18 #13

P: 13
I have done some tweaking on the report and have it doing what I want with the exception of the first group on B7. It's not separating the first page correctly. I have attached a copy of the report. I included a item number to make sure the counts were correct per page and they are with the exception of the first 3 pages. I have tried everything I know but cant get the first group to separate correctly.
Oct 8 '18 #14

PhilOfWalton
Expert 100+
P: 1,376
No report attached.

Please read Post 13

Phil
Oct 8 '18 #15

P: 13
Reformatted the attachment and it is keeps saying upload failed. I'll keep trying. I have Never had this problem before.
Oct 8 '18 #16

PhilOfWalton
Expert 100+
P: 1,376
It may be too big. There is a limit on file sizes shown at the bottom of the "Manage Attachments" tab.

Have you tried zipping it?

Phil
Oct 8 '18 #17

P: 13
I was able to scan the three pages I am having problems with but not the whole report. I tried zipping it and converting it to other file types and it would not let me attach. Its weird because I attached the whole report the other day.
Attached Files
File Type: pdf 20181008104337499.pdf (289.6 KB, 4 views)
Oct 8 '18 #18

PhilOfWalton
Expert 100+
P: 1,376
Sorry, this is the third and final time I shall ask you to respond to Post 13.
Oct 8 '18 #19

P: 13
Just sent a reply. Been having problems with attachment feature.
Oct 8 '18 #20

Post your reply

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