I have a report with four fields; NAME,HIV,HBV,HCV,RPR. Apart from the NAME the others represent diseases. In this report each person represented by the NAME would tested for the four (4) disease markers. The result is indicated by either 1 which represents a negative result or 2 which represents a positive result. therefore, if an individual is negative in all the disease markers and another one is say positive for HIV, the report will appear like this;
NAME HIV HBV HCV RPR
Ben 1 1 1 1
Jane 2 2 1 1
Sila 1 2 2 1
etc
Now the task is to calculate the disease prevalence for each disease marker which has tested positive or has 2 indicated. the general formular is e.g for HIV the number of persons tested positive for HIV Divided by the total number of persons tested Multiplied by 100 to get percentage. according to our table one person tested positive to HIV out of the Three. therefore
HIV = 1/3 x 100 = 33%
HBV = 2/3 x 100 = 66%
HCV = 1/3 x 100 = 33%
RPR = 0 = 0%
The above calculations will show the disease prevalence and need to be displayed on the report .
How can I compute the above calculations using VBA Code in the report?

Bright Mulenga


Bmulenga

__________________
Bmulenga

in a vague answer to your question you can store all the data in a table, use a query to look it up, and use an if statement to see what calculation needs to be done (if any) and then store the result in a variable and stick all that data in a make table, that is the first macro the second macro opens a report bound to the make table, actually the first macro deletes all data in the make table, the second macro appends the make table, the third macro opens a report bound to the make table...

When I say make table what i mean is it is a table that you need to create manually with no relationship to any other table, its just sitting there for the purpose of solving your problem, you delete all data and append it every time you use it, i use them frequently for complex problem solving, there may be other ways of doing it but whatever works...

hope that helps
scott

Hi,

   The proper way to store the positive and negative results would be with a check box and 0 for No and -1 for Yes. That being said, you have already indicated the method to find this answer.

   Create a report, then drop 4 text boxes (percentage format) on the report labeled with the test name, then on the report Detail section On Format event, put code like this, just change the name of the test:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim iCount As Integer
Dim iTotal As Integer
Dim lResult As Long

sSQL = "SELECT * FROM tblName" 'this is what I called the table.

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection

If rs.RecordCount <> 0 Then
rs.MoveFirst
    Do Until rs.EOF
    If rs("HIV") = "2" Then
        iCount = iCount + 1
    End If
    iTotal = iTotal + 1
    rs.MoveNext
    Loop
End If

rs.Close

lResult = (iCount / iTotal) * 100

Me.HIVText = lResult


HTH


mmcdonal

P.S. I used a text data type in my table since the numbers were not having any arithmetic calculations carried out against them. That is why iCount is looking for "2".

If you used a numeric data type in your table, then change the code to this:

If rs("HIV") = 2 Then



mmcdonal