returning values to reports from visual basic 1

[gatorgal]

I am trying to generate three values based on the possible outcomes for a variable (fred): 1: count of all values where fred=1. (1=yes)
2: count of all values where fred <>9 (9=not applicable)
3: percent of all values for fred where fred=1 divided by all values where fred<>9.

Here is my attempt to generate these numbers in visual basic, but I don't know how to get those three numbers back into my access report. Do I create controls, etc? I haven't a clue here.

Dim countall As Integer
Dim countone As Integer

Function prcntgc()

DoCmd.OpenTable

countall = 0
countone = 0

Do While GC <> 9
countall = countall + 1
Do While GC = 1
countone = count1 + 1
Loop
Loop

prcntgc = (count1 / countall) * 100

End Function

Thanks,
Toni

 

[CosmoKramer]

Create 3 text boxes in the report footer with the control sources something like this:

=DCount("fred","yourtable","fred = 1")

=DCount(&quot;fred&quot;,&quot;yourtable&quot;,&quot;fred <> 9&quot;)

=(DCount(&quot;fred&quot;,&quot;yourtable&quot;,&quot;fred = 1&quot;)) / (DCount(&quot;fred&quot;,&quot;yourtable&quot;,&quot;fred <> 9&quot;))

 

[gatorgal]

Thanks sooo much! It works!!!!

Is there a way to do this by sections, too?

Toni

 

[CosmoKramer]

Sure, just put the code in group footers and they will work there too....

 

[gatorgal]

I don't know what I'm doing wrong. I get the same total numbers in all three group footers as I do in the report footer. Those numbers are correct for the report footer. But, in group 1, there are 4 records. Two have values of '9', so are disregarded. One has a '1', and another has a '2' (inconclusive). So I should get 1 'yes', 2 total (1 yes, and 1 inconclusive) and .50 for my proportion. Do I need to change my dcount statement to have my group code in some way?

 

[gatorgal]

I figured it out. In the third part of the dcount, use 'AND' to specify multiple criteria for levels.

 

[CosmoKramer]

Sorry about that....My bad...I was working on something else when I responded earlier and forgot to mention that....