IIF Statements Using multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I am trying to accomplish:
I am tracking patients on a once every 3 Month basis after an initial
assessment. If they "Pass" two visits in a row they will no longer be
tracked. But I want to capture the patients who have not "Passed" until they
do "Pass" (again they have to pass two visits in a row.)


I am not sure if my tables are set up correctly, but this is what I have:
I have two tables; First table tblInitialSettings: InitialDate (Date field),
InitialResult(pass/fail). Second table tblFollowUp: FollowUpDate (Date
Field), FollowUpType (cboBox w/"3 Month", "6 Month", "9 Month",.......,"4
Years", FollowUpResult (Pass/Fail).

I have been trying to write IIF Statements but I get no where. For example:

IIF(IIF([InitialResult]="Pass" and IIF([FollowUpType] = "3 Month" and
IIF([FollowUpResult]="Pass")))........

I am confused. Again, I may have set up my tables wrong. Short of just
putting check boxes on my forms that state "3 Month Follow Up Pass"/"3 Month
Follow Up Fail" I am at a loss (and I don't have any more hair!) =)

Any help would be greatly appreciated. Thanks! Ryan
 
I would have the following tables:

tblPatient
PatientID
PatientName
PatientEtc...

tblPatientVisit
PVID
PatientID
VisitDate
VisitCatgory (Initial, Followup, etc)
Pass (Y/N)

The gist here is that you don't need a table for every visit. Instead,
store ALL visits in just one table. I made some sample fields here, but you
could do other things. The tricky part is still the two-in-a-row, but at
least your data is in one place to make the comparisons.
 
Steve,
First off, Love FMS great website/company. Bought the Stat analyzer,
thinking about getting the Source Book

I do actually have a third table that is tblDemographics. I guess I could
bring the Initial and Follow Up table together, but it still leaves me unable
to find records that have "Pass" two times in a row.

I know how to write IIF Statements, but wasn't sure if there was the ability
to write an IIF within an IIF.

For example: IIF(([InitialResult]="Pass" and IIF([FollowUpResult]="Pass"),
"Pass", "Fail"). I know that this is wrong (I've tried it!) but wasn't sure
if there was another way that I could do something like this.

I think what I will end up trying is write a query that is based on the
initial and then "3 Month" and call this "qry3MonthFollowUp". Then I will
write another query based on the qry3MonthFollowUp by adding 6 Month Criteria
to it. I haven't tried that, I will post back if it works. (I hope to post!)

Thanks again for your advice, Ryan
 
Back
Top