Solved

Trying to get "Month/Year" to "Month/Year" ( first and last of a While Loop ) as a header and the "months" below it as anchor links to its respective group month header.

Posted on 2012-04-04
7
340 Views
Last Modified: 2012-04-06
With the help of a resident expert ( Shout out to Robert ) I have been able to output:

January 2012
1/12/12 Alpha Party field 2 field 3
1/14/12 Beta Party field 2 field 3
1/22/12 Gamma Party field 2 field 3
1/29/12 Delta Party field 2 field 3

February 2012
2/2/12 Alpha Party field 2 field 3
2/18/12 Beta Party field 2 field 3
2/23/12 Gamma Party field 2 field 3
2/26/12 Delta Party field 2 field 3

I am trying to create a header above it that will dynamically output the first month of the loop to the last month of the loop and then have anchor links to all months to its respective group header links.

i.e.
January 2012 to March 2012  < The header
January 2012 | February 2012 | March 2012  < The anchor links to the grouped months below

Can this be done?

Thank you in advance experts.
0
Comment
Question by:colonelblue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37807888
Hey, by accident I found this. To make it easier for people to help it would have been good to add a link to the previous question, although through your profile page it can be found of course.

So you're looking to add <a href='#anchorxxx'>...</a> tags as a header and <a name='anchorxxx'> in the contents to jump to.

For the first part you can use an adapted version of your SQL which only returns all different groups instead of all the data.

So something like this:

SELECT DISTINCT MONTH(start) AS 'Month', YEAR(start) AS 'Year'
FROM dbo.Events 
ORDER BY Year, Month ASC

Open in new window


Reading that from a separate recordset (use another Execute) you can create href's to anchors that you'll write out later on. It's probably easiest to save these to a string variable and then output at once.

something like:

strHrefs = strHrefs & " | <a href='#ANCHOR" & Fields("Year") & Fields("Month") & "'>" & MonthName(Fields("Month")) & " " & Fields("Year") & "</a>"

Open in new window


Then, in your existing code, add those same names in an anchor tag, just before the group header itself. something like:

<a name='ANCHOR<%= Year(Fields("Start")) & Fields("Month") %>'>

Open in new window


Also, in the first loop you can save the first and last group header in separate variables to display the first line of the header.

Does this make any sense?
0
 

Author Comment

by:colonelblue
ID: 37808692
Robert, ain't I lucky :)!!
Thank you,.

i spent the last few hours figuring how to do this and with your guidance i was able to do 75% of it!

I created another recordset as you had suggested (I did not think of that earlier)
I created anchor links within the first call to the previous recordset using the month(number) as the anchor # link.

So I got that all going yeaaa!

BUT...

How do I get the first month and the last month so that I can get it to stay on top of the page:
January 2012 to March 2012 ?

And  finally on the newly created recordset that creates the anchor links , I appended a pipe bar after each month, but how do I make it so that the last month does not have a pipe bar next to it.

Again thank you so much. It's satisfying to figure things through. :)
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 37809638
Fantastic! You have got to believe, it's satisfying for me as well to see your enthusiastic reaction.

Basically I would suggest to do almost the same as with the group headers to save the first and last month. So create 2 new variables (say strFirst and strLast) and init with empty string. Then in the new loop, store the month name and the year every time in strLast, but only in strFirst when that is still empty. So something like:

strLast = MonthName(Fields("Month")) & " " & Fields("Year")
if strFirst = "" Then strFirst = strLast

Open in new window


To get rid of the last separator you could use Left or Right (in combination with Len) to split it off, or you can prefix instead of append it and then only do that if strHrefs is empty, or use this little trick I like:

Dim strSep
strSep = ""
While Not recs.eof
  strHrefs = strHrefs & strSep & "...."
  strSep = " | "
  recs.MoveNext
Wend

Open in new window


So this prefixes the output string with the separator string each time, but the first time that separator string is still empty.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:colonelblue
ID: 37816563
Hey guy. I apologize I got pulled aside and will try your solution right away. Will let you know!
Thank you again! :)
0
 

Author Comment

by:colonelblue
ID: 37817561
Robert! I did it!

The first part I created another recordset , loop and utilized creating those capture variables.
Worked like a charm.

Then for the pipeline, going off your cue, I placed a capture in a previous loop that displayed the first month to the last month.
I placed this:
<% strLast = strPipe %>
Then before writing the pipeline in the loop that created the anchor links.
I placed an if/else to see if the current month's value did not equal strPipe.
If it did not then write the pipeline.

Woo-Hoo. I feel like Luke and you're Yoda.:)

I was wondering though, to avoid any critique from anyone who sees my code, is it OK to have more than one recordset? Does several "slow down" or take away in any way?

Again thank you so much!
You're a God send! :)
0
 

Author Closing Comment

by:colonelblue
ID: 37817569
You're the best Robert.
GRATITUDE!
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37818046
Nice!

Well, the 'header' loop works with a much smaller set of data so that shouldn't be a problem but if you wouldn't want a second loop for some reason, one alternative would be to create a variable and put all your report output in it, then you can put the functionality of the new loop inside the 'big' loop (you'd have to modify it a bit to account for the different data) and afterwards output everything at once.

The big advantage of having 2 loops though, might be when you start working with filtering. So just as an example, your header could show links to all years that are present in the database, but the data is only shown for a selected year.

Have fun with it!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question