Query Design

[kitty77]

How would I write the following...
Status: If [results] like "*pass*" then "Y", if [results] like "*fail*" then "N"

I'm using this in the query design.

 

[June7]

What other values are possible? Pass and Fail are not the complete strings, they are embedded in longer strings?

Status: IIf([results] LIKE "*pass*", "Y", "N")

 

[kitty77]

That works, however, I need it to be if pass, then 'Y", if fail then "N" else blank.
Because, some of the data does not meet that. Make sense?

 

[June7]

Status: IIf([results] LIKE "*pass*", "Y", IIf([results] LIKE "*fail*", "N", Null))

or

Status: Switch([results] LIKE "*pass*", "Y", [results] LIKE "*fail*", "N", True, Null)

 

[kitty77]

Perfect!!!

 

[kitty77]

I have another condition I would like to check for...
Status: IIf([Mresults] Like "*meets*","Pass",IIf([Mresults] Like "*fail*","Fail",Null)) (this is what I'm currently using and working)

IIf([Mlink] Is Null,"No") (I would like to add this to the above)

So that it would check for this too. Can't seem to get to work.

Thanks...

 

[Cronk]

Use IsNull() not Is Null

 

[Pat Hartman]

"Is Null" is SQLsyntax and is used only in a query. When you need to check for null using VBA, use the IsNull() function as Cronk suggested.

 

[kitty77]

Ok, how would I combine my two statements then?

 

[Pat Hartman]

That is not enough information. Write out the logic using pseudo code and we will convert it to the IIf() We need to know where the test for Mlink would fall. Is it to replace the Status = Null line with one more test?

So far you have

If [Mresults] Like "*meets*" Then
    Status = "Pass"
Else
    If [Mresults] Like "*fail*" Then
        Status = "Fail"
    Else
        Status = Null
    End If
End If

 

[kitty77]

Yes, to replace the status. I guess it would be first. If Mlink is null, then status is null. Then the rest of the code wouldn't matter.
So, yes first.

 

[Pat Hartman]

Status: IIf(IsNull(Mlink), Null, IIf([Mresults] Like "*meets*","Pass",IIf([Mresults] Like "*fail*","Fail",Null))

Break this statement apart so you can see how it works. IIf(condition, true path, false path)

Either the true path or the false path can be replaced with a complete "IIf(condition, true path, false path)" I don't remember how deep you can go but most people can't read anything past 4 conditions. At that point, you should probably create a UDF.

 

[kitty77]

Absolutely Perfect!!

 

[Pat Hartman]

Excellent

 

[kitty77]

How would I combine this?

Report: IIf([Msamplenumber1] Like "*H*","In Process",[Mreportid]) (this works perfect. Want to add the below to the this)

or IIf([Msamplenumber2] Like "*H*"
or IIf([Msamplenumber3] Like "*H*"
or IIf([Msamplenumber4] Like "*H*"

 

[Pat Hartman]

IIf(condition, true path, false path)

The IIF() has three operands any or all of them can be replaced by complex values. So, condition can be compound ie.
IIf(somevalue Like "*H" OR somevalue2 Like "*" OR somevalue3 Like "*", true path, false path)

The true and false paths can be replaced by complete IIf() statements. You just have to watch your parentheses.
IIf(condition, IIf(condition, true path, false path), false path)
or
IIf(condition, true path, IIf(condition, true path, false path))
or
IIf(condition, IIf(condition, true path, IIf(condition, true path, false path)), false path) -- it can get really hard to understand, really quickly so at some point, you should create a UDF which will be much easier to construct and be more understandable by a human.

I would normalize my table. You have a repeating group. When you have more than one of something, you have many and many requires a child table.

What you are doing with Like is extremely inefficient so I hope you don't have a lot of rows in your table. The Like "*xxxx" forces a full table scan. There are better methods but they require tablizing the data. You would create a table of the code values and add a group field. Then rather than using Like *, you could use = to a group value.

 

[kitty77]

Perfect!!!!

 

[Pat Hartman]

See how easy it was once you understand the basic construct

 

[kitty77]

Yes, I agree