iff and statement 1

[aarellano]

Hello, I am having a bit of a diffucult time with this statement
I am trying to do an "iif" "and" statement but I keep getting an error here is what I am trying

iif(wkctr like 'IND*' and DPTNO between 42 and 44, tmlb)

But I keep on getting Error if I take the

and DPTNO between 42 and 44

it works fine.

what would be the way to go about this one?

 

[JBinQLD]

G'day fella,

An immediate if (IIF) needs a true action and an else action, you only have a true part so it doesn't know what to do if the condition is not met.

When debugging IIFs I sometimes find it helps to begin with ensuring the condition / test is being evaulated, then adjusting the returns when I'm happy. So test:

iif(wkctr like 'IND*' and DPTNO between 42 and 44, "Good","Bad")

and ensure that good or bad is returned depending on the values of wkctr and DPTNO you feed it...

Probably a silly question, but DPTNO is a number field right?

JB

 

[aarellano]

JB,
thanks for taking the time,
Yes, the DPTNO is a number field. When I tried to use your suggestion I got "Bad" and instead of "Goog" I got "Error

 

[JBinQLD]

Try with some brackets then:

iif([red](([/red]wkctr like 'IND*'[red])[/red] and [red]([/red]DPTNO between 42 and 44[red]))[/red], "Good","Bad")

 

[aarellano]

arggg
very good suggestion but the same result

 

[JBinQLD]

Just mimicked the same thing here and works fine with or without brackets. Are you using this in VBA or as a field in a query?

Do the components of the check work:

iif(DPTNO between 42 and 44, "Good","Bad")

and

iif(wkctr like 'IND*', "Good","Bad")

 

[aarellano]

argggg

I think you found it!!!!!!

it appears to be the dptno. could it be a text field then??

 

[JBinQLD]

Check in the table mate. If it is text and cannot be changed then change your iif statement to:

iif(wkctr like 'IND*' and DPTNO In('42', '43', '44'), "Good","Bad")

JB

 

[aarellano]

thank you so very much!!!! You are the best!!!!!!