[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
343 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 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