Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: IFF Statement - Validation / QA Data

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    iif(ISNULL([t]) AND ISNULL([n]) AND ISNULL([m]), "unspecified",
    iif(SNULL([t]) OR ISNULL([n]) OR ISNULL([m]), "incomplete",
    iif(Mid$([t], 2, 1) = "x" OR (repeat formula), "Undeceive", "Complete")), "Complete")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •