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
325 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
  • 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now