IIf expression

[lwarren1968]

I am not an expert, so please be nice!

I'm trying to write an express for if [Value 2] has no records then return "" otherwise return data from field [,sw_] & [Label 2] & [=1] & [Value 2]. I can't seem to get it to work as for those that are Null are still returning data from fields [,sw_] & [Label 2] & [=1]

Expr2: IIf(([Value 2]) Is Not Null,[,sw_] & [Label 2] & [=1] & [Value 2],"")

 

[Ranman256]

try:

IIf(IsNull([Value 2]),"",[sw_] & [Label 2] & "1" & [Value 2])

 

[Beetle]

It could be that [Value 2] is not actually Null, but rather an empty (zero length) string. Try;

IIf(Nz([Value 2], "")="","",[,sw_] & [Label 2] & [=1] & [Value 2])

On a side note, you should consider removing special characters and spaces from your field names if possible. It just causes problems in other parts of your application.

 

[lwarren1968]

It returned sw_SLEEVE1 when it should have returned no value or been blank [Value 2) for those with no data

 

[lwarren1968]

That actually worked. Thank you so much for being patient with me.

 

[Beetle]

When you say "It returned...", what do you mean by It? What did you try?

 

[lwarren1968]

It worked! Thank you so much!

 

[Beetle]

What worked? There were two different solutions offered. It may be helpful to future readers of this thread to know which one worked.

 

[Minty]

I really hope you didn't choose

[,sw_]

as a field name by choice.
As has been suggested Change your field names to something more meaningful without the weird characters, and it will probably make troubleshooting this a lot easier.

Just to prove a point try this

IIf(Len([Value 2] & "") < 1, "", ([,sw_] & [Label 2] & [=1] & [Value 2]))

 

[lwarren1968]

Although the field was black it had to be a zero string like you thought. Thank you.

IIf(Nz([Value 2], "")="","",[,sw_] & [Label 2] & [=1] & [Value 2])

 

[lwarren1968]

I agree with your suggestion regarding using weird characters. Field names are being changed in my database. The file I received is a big dump of information that will be altered into something more meaningful for future use. Thank you again!