Link to home
Start Free TrialLog in
Avatar of colonelblue
colonelblue

asked on

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.

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.
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

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?
Avatar of colonelblue
colonelblue

ASKER

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. :)
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey guy. I apologize I got pulled aside and will try your solution right away. Will let you know!
Thank you again! :)
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! :)
You're the best Robert.
GRATITUDE!
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!