IIf expression (1 Viewer)

lwarren1968

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #1

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

Well-known member

Local time

Today, 06:48

Joined

Apr 9, 2015

Messages

4,339

• May 8, 2018

• #2

try:

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

 

B

Beetle

Duly Registered Boozer

Local time

Today, 04:48

Joined

Apr 30, 2011

Messages

1,808

• May 8, 2018

• #3

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

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #4

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

 

lwarren1968

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #5

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

 

B

Beetle

Duly Registered Boozer

Local time

Today, 04:48

Joined

Apr 30, 2011

Messages

1,808

• May 8, 2018

• #6

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

 

lwarren1968

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #7

It worked! Thank you so much!

 

B

Beetle

Duly Registered Boozer

Local time

Today, 04:48

Joined

Apr 30, 2011

Messages

1,808

• May 8, 2018

• #8

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

 

Minty

AWF VIP

Local time

Today, 11:48

Joined

Jul 26, 2013

Messages

10,367

• May 8, 2018

• #9

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

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #10

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

Registered User.

Local time

Today, 03:48

Joined

Jan 18, 2013

Messages

77

• May 8, 2018

• #11

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!