IIF statement to manipulate data - for QA

[pearce64]

Hi all - any help much appreciated

I' am trying to write a Query that will QA staging Data for Patients, in the output shown below. The sample below is only a small subset of the full dataset., and the records contained in my table.

Either a query in QBF or a small function in VBA I don't mind which.

I have 3 columns T,N,M populated with staging details. The forth column is simulated output I require.

outputs
1. If fields [T], And [n] And [m] are Null, output = unspecified.
2. If either one or more fields are not populated = Incomplete.
3. If the second digit in either [t] or [n] or [m] contains an x, output = Undeceive.
4. Any other combinations, left output = Complete.

T N M Output
T4 N2 M0 complete
T4 N2 MX Undeceive
T4 N0 M0 Complete
T4 N2 M1 Complete
Unspecified
T4 N1 M1 Complete
Unspecified
Unspecified
TX N0 M0 Undeceive
Unspecified
Unspecified
T2 N0 M0 Complete
T1 N0 M0 Complete
T2 N2 M1
T3 Incomplete
T2 N1 M0 Complete
T2 M0 Incomplete
T2 N2 MX Undeceive

 

[PHV]

Something like this ?
IIf(Len([T] & [N] & [M] & "")=0, "Unspecified",
IIf(IsNull([T] + [N] + [M]), "Incomplete",
IIf([T] Like "?X*" Or [N] Like "?X*" Or [M] Like "?X*", "Undeceive", "Complete"))) As Output


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pearce64]

thank you very much -