UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Formatting A Dynamic Header And Detail, Access 2016    
 
   
LagoDavid
post Jul 12 2018, 03:14 PM
Post#21



Posts: 446
Joined: 12-October 03
From: Texas


And, I forgot to respond about the layout controls group. Yes those layouts frustrate the [censored] out of me. They came with the Access report template. I did not add them. I have to invest some time and figure out how to use and or delete them. It seems like the template must have about 50 different layouts according to how difficult it is to get rid of them. They would likely be useful if I learned how to use them, but now they are as you said, just making it more difficult.
Go to the top of the page
 
RJD
post Jul 12 2018, 03:36 PM
Post#22


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


I'm guessing you already changed the crosstab creation routine, but this is what it should look like ...

CODE
strOrderBy = _
            "ORDER BY " & _
            "tmptblReportSampleResults.ParameterDefaultSort, " & _
            "tmptblReportSampleResults.SampleSort " & _
            "PIVOT tmptblColumnHeads.SortID[color="#FF0000"] In(1,2,3,4,5,6,7)[/color]"

To deal with the grouped controls (yes, I know, Access likes to put those in in some instances - and it annoys me) select the layout group, and on the ribbon choose the Arrange tab and click on Remove Layout.

Looks like you are on your way now. Anything else we can do for you at this stage? Good luck on the next stage, and let us kn ow if we can assist in the future.

isladogs: I'll look up the demo for the dynamic crosstab report later, and also send you a 2-page writeup on the approach.

Regards,
Joe
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 11:51 PM
Post#23



Posts: 446
Joined: 12-October 03
From: Texas


You will get a laugh out of this probably.

I saw the "color" stuff in your post and thought meant that was supposed to be in my code so I copied and pasted it. It wasn't until I got the error that I realized you were trying to color the IN statement in your post. Gives new meaning to Access for Dummies!

Seriously I do now have a new problem. Now that that the report actually runs it has exposed an new problem in my code that I don't how to handle. And if appropriate I will start a new thread.

If I create a report and leave it open in Report View, and then attempt a new report via my report setup form, I get a system error because the temporary table the XTab query uses is locked by the first report. In my code I delete the temp table each time, re-create it, and then append the new records to it. So when the code attempts to delete the temp table, it fails because the table is locked by the query in the first report.

Ideally I would like to have 2 or 3 reports open at the same time to compare the data against each other. How would you professionals handle that?
Go to the top of the page
 
LagoDavid
post Jul 13 2018, 10:38 AM
Post#24



Posts: 446
Joined: 12-October 03
From: Texas


Joe, never mind the last question. I decided to add VBA to output the report to PDF and then close the report, so there is no longer a problem with the table being locked.
Go to the top of the page
 
RJD
post Jul 13 2018, 11:42 AM
Post#25


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Hi: Glad you got that fixed. That was going to be my suggestion next anyway. Good fix.

Sorry about my glitch on the code. I encased the In area to be red, then failed to read how it was displayed after posting. My bad! But you caught that.

Continued success with your project. Let us know if we can be of further assistance.

Incidentally, I shared your posted db (with hidden identifiers) to show someone else how the generalized crosstab approach works.

Regards,
Joe
Go to the top of the page
 
RJD
post Jul 13 2018, 11:54 AM
Post#26


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Oh, and I saw from your other thread that you got the pdf issue fixed by using VBA to export the file. Good catch/approach.

Continued success ... thumbup.gif

Joe
Go to the top of the page
 
RJD
post Jul 13 2018, 12:17 PM
Post#27


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Oh, and yet one more thing. Here is a re-write of the date sequencing query to avoid the domain function (DCount). This may speed up things, but that is an empirical question. You might want to try it ...

CODE
SELECT [Sample Date], (SELECT Count(*) FROM qryDatesInRange As X WHERE qryDatesInRange.[Sample Date] >= X.[Sample Date]) AS Seq
FROM qryDatesInRange
ORDER BY [Sample Date];

Joe
Go to the top of the page
 
isladogs
post Jul 13 2018, 05:35 PM
Post#28



Posts: 559
Joined: 4-June 18
From: Somerset, UK


Joe

Attached is a screenshot of the report as it appears on my home computer - tried to attach in my reply to your PM but can't work out how to do so


Attached File(s)
Attached File  RJDCrosstabReport.PNG ( 12.25K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Jul 13 2018, 07:02 PM
Post#29


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Hmmm... I can't duplicate that. Maybe it has something to do with European vs US dates. Perhaps the date cannot be rendered in European dates? Just a wild guess.

You might back up and see what the query of date sequences looks like on your computer.

As I said, I can't duplicate your posted image.

HTH
Joe
Go to the top of the page
 
RJD
post Jul 13 2018, 07:05 PM
Post#30


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


isladogs: Here's the file you couldn't download elsewhere...

And let's not continue this thread. If we need to continue, let's start another thread and not hijack LagoDavid's thread ...

Joe
Attached File(s)
Attached File  DynamicReport_DemoMaster_Rev_20180713.zip ( 165.2K )Number of downloads: 2
 
Go to the top of the page
 
isladogs
post Jul 13 2018, 07:31 PM
Post#31



Posts: 559
Joined: 4-June 18
From: Somerset, UK


Agreed ... but just to say that in the new version the report opens in less than 2 seconds & renders perfectly

Attached File  Capture.PNG ( 11.82K )Number of downloads: 1


Just about to log off.
I am out for much of this weekend but will look at it properly when I can

@LogoDavid
Apologies for sidetracking your thread


This post has been edited by isladogs: Jul 13 2018, 07:33 PM
Go to the top of the page
 
RJD
post Jul 13 2018, 09:14 PM
Post#32


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


isladogs: Thanks for checking on the new version. Looks like getting around the domain functions made a big improvement.

Have a good weekend. Let me know though a PM if you want to continue and we can start a new thread.

Regards,
Joe

And my apologies also, David, for hijacking your thread - and we wish you continued success with your project. Let us know if we can be of further assistance.

Joe
Go to the top of the page
 
LagoDavid
post Jul 16 2018, 11:26 PM
Post#33



Posts: 446
Joined: 12-October 03
From: Texas


PLEASE NO APOLOGIES! I enjoyed and learned from your back and forth!
Go to the top of the page
 
RJD
post Jul 16 2018, 11:44 PM
Post#34


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


thumbup.gif
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    15th October 2018 - 07:45 AM