Trying to creae IF AND statement

[Guest]

Hi all,

I'm trying to create an IIF / AND statement that does the following:

My database tracks the success rates of a certain pharma drug based on a
single entry: a user will either enter "C" or "F" (cure of fail) into a
designated field title "Cure of Fail". A separate summary then displays the
percentage of those who were cured and those who failed (i.e. out of 100
patients, 95% were cured). That part I have figured out. BUT, I would now
like to run some more in-depth analysis of those results based on different
parameters from a variety of different fields. For instance, of those who
had a "C", what % of them were female? Another example: What percentage of
'Asians' ('Race field') were cured?

The IIF/AND statement would have to read similar to the following:
(example only) If "Race" = 'Asian' AND "Cure or Fail" = 'C', then display
the percentage of Asian's with a 'C' status out of the entire cured
population (NOT everyone in the DB...only those who have a 'C' status).

Can anyone help me figure this out?

Thanks

 

[Kipp Woodard]

I suggest you use the Open event of the report. Use DCount that returns the
count of records that would be in the report (same criteria as the report
would have). Prompt the user with the count, using MsgBox. If the user
responds that they don't wish to continue, then set the Cancel argument of
the event to True.

It would look something like this:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)

Dim lRecordCount As Long
Dim lUserResponse As VbMsgBoxResult

lRecordCount = Nz(DCount("1", "qryReportSource"), 0)

lUserResponse = MsgBox("There are " & lRecordCount & " records." &
String(2, vbLf) & _
"Do you want to view the report?", vbYesNo)

If Not lUserResponse = vbYes Then
Cancel = True
End If

End Sub

 

[John Vinson]

Hi all,

I'm trying to create an IIF / AND statement that does the following:

My database tracks the success rates of a certain pharma drug based on a
single entry: a user will either enter "C" or "F" (cure of fail) into a
designated field title "Cure of Fail". A separate summary then displays the
percentage of those who were cured and those who failed (i.e. out of 100
patients, 95% were cured). That part I have figured out. BUT, I would now
like to run some more in-depth analysis of those results based on different
parameters from a variety of different fields. For instance, of those who
had a "C", what % of them were female? Another example: What percentage of
'Asians' ('Race field') were cured?

The IIF/AND statement would have to read similar to the following:
(example only) If "Race" = 'Asian' AND "Cure or Fail" = 'C', then display
the percentage of Asian's with a 'C' status out of the entire cured
population (NOT everyone in the DB...only those who have a 'C' status).

Can anyone help me figure this out?

Thanks

I think you just need to use a criterion to filter out only the C
results and a DCount() function call to get the count of subsets:

SELECT DCount("*", "[yourtable]", "[Cure of Fail] = 'C' AND [Sex] =
'F'") / Count(*) As PercentFemale, DCount("*", "[yourtable]", "[Cure
of Fail] = 'C' AND [Race Field] = 'Asian'") AS PercentAsian
FROM yourtable
WHERE [Cure of Fail] = 'C';


John W. Vinson[MVP]