IIf STATEMENT - MS QUERY (1 Viewer)

lwarren1968

Registered User.

Local time

Today, 04:07

Joined

Jan 18, 2013

Messages

77

• Dec 16, 2019

• #1

I have an express below that works well when the data meets the specified criteria. However, I need all data to be returned not just those adjusted based on the expression below. What am I missing?


Expr2: IIf(Len([Expr1])=13,Left([Expr1],12),IIf(Len([Expr1])=12,Left([Expr1],11)))

 

theDBguy

I’m here to help

Staff member

Local time

Today, 04:07

Joined

Oct 29, 2018

Messages

21,455

• Dec 16, 2019

• #2

Hi. I'm not sure I understand, but maybe try this?


Expr2: IIf(Len([Expr1])=13,Left([Expr1],12),IIf(Len([Expr1])=12,Left([Expr1],11),[Expr1]))

 

lwarren1968

Registered User.

Local time

Today, 04:07

Joined

Jan 18, 2013

Messages

77

• Dec 16, 2019

• #3

That is exactly what I needed. Works perfectly. Thank you!

 

theDBguy

I’m here to help

Staff member

Local time

Today, 04:07

Joined

Oct 29, 2018

Messages

21,455

• Dec 16, 2019

• #4

lwarren1968 said:

That is exactly what I needed. Works perfectly. Thank you!

Click to expand...

Lucky guess. Glad to hear you got it to work. Good luck with your project.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 19:07

Joined

May 7, 2009

Messages

19,230

• Dec 17, 2019

• #5

to simplify your IIFs:

Expr2: IIf(Len([Expr1])>11, Left([Expr1],LEN([Expr1])-1), [Expr1])