Solved IIF with many

[murray83]

Have this following code which works fine, but now need to have it also work for more criteria, but i tried as shown but it didn't do i need to use AND instead of Or

Working Code
[CODE]=IIf([Text298] Like '190003*',"Please tip as a loose load, slip sheet has invalid [/CODE]

My Broken Code

=IIf([Text298] Like '190003*' Or '230131*' Or '30413*',"Please tip as a loose load, slip sheet has invalid

Cheers All

 

[sonic8]

You can only link complete boolean expressions with OR:

=IIf([Text298] Like '190003*' Or [Text298] Like '230131*' Or [Text298] Like '30413*',"Please tip as a loose load, slip sheet has invalid

 

[Gasman]

do i need to use AND instead of Or

Only you know that
TBH I cannot see that first block of code even working?

IIf([Text298] Like '190003*',"Please tip as a loose load", "slip sheet has invalid")

From what you have written for the second code, it would be OR, as they cannot be all those numbers at the same time.
You need to repeat the test though

IIf([Text298] Like '190003*' OR [Text298] Like '230131*' ,"Please tip as a loose load", "slip sheet has invalid")

 

[murray83]

Only you know that
TBH I cannot see that first block of code even working?

IIf([Text298] Like '190003*',"Please tip as a loose load", "slip sheet has invalid")

From what you have written for the second code, it would be OR, as they cannot be all those numbers at the same time.
You need to repeat the test though

IIf([Text298] Like '190003*' OR [Text298] Like '230131*' ,"Please tip as a loose load", "slip sheet has invalid")

The first one does, if the textbox listed displays the correct text then it shows another text box but if not then it is hidden, but i did try but it shows all the time

 

[murray83]

This is the example when it works as required

 

[sonic8]

@murray83 , did you try the suggestions for rewriting the IIF statement by @Gasman and myself?

 

[murray83]

so sorry didnt even see you had made a change to code ill try it right now

 

[murray83]

and yes worked a charm cheers all

 

[Pat Hartman]

If you have to use "like", you have mushed multiple attributes into a single column. Each column should be atomic. That means, it should contain one and only one attribute. A far better solution for a task like this is to use a table that has an additional column. Then rather than using Like, you can look up the "type" and use = instead. It also means that you don't need multiple conditions. You just look at the category value.

PS, not naming your controls with logical names is just plain sloppy. You probably aren't going to go back and change all your existing code but there is no reason to not mend your ways and never do this again. Text498 should probably be LongSupplierName or something like that. Even better would be to add a "type" code to the Supplier table. Then the code would be
Me.Message = IIf(Me.SupplierType = "??" ,"Please tip as a loose load", "slip sheet has invalid")

 

[murray83]

very true Pat, and i did change a name as the request given to me required another text box which i duly named something appropriate ( as the db was made before me ) but again cheers for all ideas and help

 

[murray83]

sorry for post after saying resolevd but have issue with this

=IIf([Text106] Like 'loose*',"Use CAS GDS IN")

It should display Use CAS GDS IN when the textbox106 ( yes i know silly name, not my fault ) but all i get is blank but then for the following

=IIf([Text106] Like 'pallet*',"Use PAL GDS IN")

works fine and i have no idea why.

Final thing this is on a report, cheers all

 

[Gasman]

I would hazard a guess that there is no loose?
Also where is the False part of that statement?

tt="Loosers lose too well"
? iif(tt like "loos*","Found","Not found")
Found

 

[murray83]

there is loose as the full wording is Loose Loaded but like should work, no ?

 

[Gasman]

I would have thought it would, but was suspecting perhaps a space at the start?

However, why not just test it your self when in doubt?

tt="Loose Loaded"
? tt
Loose Loaded
? iif(tt like "loos*", "Found", "Not Found")
Found

tt=" Loose Loaded"
? tt
 Loose Loaded

? iif(tt like "loos*", "Found", "Not Found")
Not Found

 

[murray83]

sorry for being dumb so would i put that in the text box source ?? not done any error catching in past

 

[Gasman]

No, in the immediate window (ctrl + G)
That is where I test anything quickly.
Debug.Print the length of Text106

 

[GPGeorge]

Here's the dilemma. Something in your data is not actually what you think it should be. You are asking people who can't see your data what that problem is.

Gasman has suggested some testing, using the Immediate Window and several variations of the suspect term to try to figure out why the stored values in your data are not what you think they should be. We can't do that testing because we don't have the data to test.

It's not a question at this point of putting something in the text box source, nor of error handling. It's a matter of you carefully analyizng values in your data.

 

[NauticalGent]

So...you're saying it's in the data...?