Dynamic heading with a sort 1

[elefantmannen]

hello,
I have a report where I have sorted the information based on specific categories. What I want to do is add a group header which have the value of one of these categories so it looks like...

CATEGORY1
everything within this category

CATEGORY2
everytihing within this category

hope Im making sense!

 

[scriverb]

This is real easy for you to do. In the report design select the Sorting and Grouping button. This will display a window where you can sort and with the Group Properties below for a sorting item, select Yes for Group Header. Close the window. You will now see that you have a new Group Header for Category. Within that Section put the controls necessary to identify the Category.

Put your detail controls as normal in the Detail Section. Now when you run the report you will see the format that you requested.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

thanks a bunch! It was the thing with getting the value into the heading I hadnt figured out but now it is fixed!

 

[elefantmannen]

hmm, ran into something else... Is it also possible to print a value in the grouping section which is based on the counting of "yes" within that specific category??

I have three categories, and there are ten questions within each. I want to print out the heading (which is completed now) and also how many "yes" answers every person has for that specific category. How do I produce a field which can present unique information for every heading??

 

[scriverb]

Yes, you can do that for any of the Group Headings and Footers. Just set the control Source to something like this:
=Sum([controlname])

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

hmmm, probably Im too stupid for this...
I added a subreport from which I can get the value of one of the categories and I guess I could do it like this. Although I believe it is an ugly work around...

The other problems remain though... How do I get it to print a different value for each heading?

 

[scriverb]

ACCESS will do that for you. Just make sure you have a control at the detail level for the field that you are summing up. It can even be invisible if you don't want to display it.

ACCESS rolls through all of the records and performs these aggregate functions before it actually goes to print. So, in the Group Header or Footer these values will be for just the field values that are triggering the section.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

I have wrestled this for a while now. I have a controll which counts the questions for each category so it sums up to ten for every category. How do I set it to just count the rows where it is a "yes" in the answer?

 

[scriverb]

Use an IIF statement:
=Sum(IIF(Me.YourControl=True, 1,0))

Update the YourControl with the name of the control with the Yes/No value. No this will sum up and returned value which will only be 1's if a Yes so in effect in is counting the number of questions that are Yes.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

Problem is I dont have yes/no as a datatype... It is actually two different text sources. Is there any way to write this code but count string instead. Sorry for the bad info...

I tried putting in "LIKE" and different stuff but it didnt work.

help me obi-wan bob, your my only hope

 

[scriverb]

Yes, if you want to count the number of occurances of a certain string use the following syntax:

=Sum(IIF(Me.YourControl=&quot;<Enter the string to count here>&quot;, 1,0))


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

I dont know if it is the Me.yourControl that is the missing link. Now I type

=Sum(IIF([svar]=&quot;Ja&quot;,1,0))

where svar is the category to count.

If I write it like this, it says &quot;bad syntax&quot;.

I have an Access book where this seems to be the way to write it, but it just doesnt work.

 

[scriverb]

You must have a control in the Detail Section of the report called svar.

=Sum(IIF(me.svar=&quot;Ja&quot;,1,0))

From what you are posting the Category control name is svar. This should not give you your count of records with a value of Ja in the control named svar.




Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

ok, I have the category controll, which is named svar. I have added another controll named checkAnswer in the detail field. How shall I bind it to the controll svar? (Now I have [svar] in the control source for checkAnswer)What I really cant figure out is the &quot;Me&quot;-thing, what is that?

 

[scriverb]

Just use the following:

=Sum(IIF([svar]=&quot;Ja&quot;,1,0))

Sorry about the Me thing. Me is a alias way of referring to the current report, form, etc. rather than saying
FORMS![formname]![controlname]

In the expression that you are using it is not accepteable.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

I really start to think there is something wrong with the entire IIF procedure on my computer. I get a syntax not valid-message for the above mentioned expression. No matter how I try to use the IIF, it fails. Tried it on two different computers and it doesnt work on any of them... Could there be something missing, in the report or somewherer else??

 

[scriverb]

If you want to test the IIF let's do this. Create a control in the Detail Section called txtBob. In the Control Source put the following: =&quot;Bob&quot;

Now in a control in the Group Header put the following in the control source:
=Sum(IIF([txtBob]=&quot;Bob&quot;,1,0))

This is very simple setup and the IIF should work.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

Hi Bob,
No sorry it doesnt work. Guess my access is all f-d up. Thanx for all the help though!

 

[scriverb]

This is my fault. Rule on summing in a header or footer. You cannot sum a calculated control. So, the control must be populated with a value from your query or table directly. So, if you can update your query with the IIF and create a fieldname and then let that be the ControlSource for your Detail Section control. Then your Sum of that control will in fact work.

New column in your query:
SvarValue: IIF([tablename].[svar] = &quot;ja&quot;, 1, 0)

Now in the detail section create control for this new query field item. Make it invisible and very small and out of the way. No need to even see it.

Now the control source for the control in the Header or Footer would just look like the following:
=Sum([SvarValue])

That should do it. You see the calculation and conversion is taking place in the query and just being displayed in the detail section. Now the Sum function will work by summing a non-calculated control. Sorry, about taking you off on a misguided direction earlier.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

hey,
dont be sorry, there is still hope!
What do you mean by &quot;New column in your query&quot;?
What I did now was creating a control with the control source &quot;IIF([tablename].[svar] = &quot;ja&quot;, 1, 0)&quot;

Was that correct?

Maybe it is obvious but I have never used IIF before.