Access Query = or like

[end922]

Greetings, I am at a loss with this and have tried several times with no success.
Using query design in Access I have a field called "New Investor" it could have a value of A or B or C or AB. In another table there if a field called "Previous Investor" This field contains value separated by a ;. Example A;B;B;AB....

I am trying to write an iif statement that says if New Investor is = to Previous Investor than "Y" else "N".
I have tried
Previously Kicked: iif([New Investor]=[Previous Investor],"Y","N")
Previously Kicked: iif([New Investor] like [Previous Investor],"Y","N")
both results return "N".

 

[HarleyQuinn]

How about trying something like this:

Previously Kicked: IIf(InStr(1,[Previous Investor],[New Investor] & ";"),"Y","N")

This assumes that the final value has a smie-colon after it, if not use the & ";" with [Previous Investor] as well.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lameid]

Previously Kicked: iif(Instr(1,NZ([Previous Investor],"~") >0, [New Investor],"Y","N")


Check out the INSTR Function Visual Basic Help Topic for more information on INSTR. I used NZ for and "~" for the first parameter so you would never have a null or zero length string. I used the tilde because I doubt you would ever have that in data. You can change it to something else.

 

[lameid]

HarleyQuinn and I crossposted... I hadn't thought about the list being delimeted. My method incorrectly hits true for AB when A or B is the new value.

At the same time I am concerned if the search string is null... I assume it is if it is a new record.

I think this better considers everything:

Previously Kicked: iif(Instr(1,NZ([Previous Investor],";") & ";" >0, [New Investor],"Y","N")

Note I used > 0 while HarleyQuinn relied on the fact that Access treats 0 as false and any other non null value as true.

 

[HarleyQuinn]

lameid - The combination of our posts seems safest, just a quick change to make it run correctly (yours still returned true for AB when A or B is the new value )

IIf(InStr(1,NZ([Previous Investor],";"),[New Investor] & ";"),"Y","N")

Again, I've assumed that the semi colon seperated field has a semi colon as the final character.


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

I'd use this:

IIf(InStr(";" & [Previous Investor] & ";", ";" & [New Investor] & ";"), "Y", "N")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

The last 2 post got it. The table that contains the previous investor may not always contain a ; at the end. Depends on the user entering the data.

Thank you much!!

E

 

[JoeAtWork]

This field contains value separated by a ;. Example A;B;B;AB....

Ah the perils of using multi-value fields in a relational database!

If at all possible, I would encourage you to normalize the database by breaking this data out into a related table, such that each value ("A","B","B","AB"...) is it's own record.

The previous posters have given you solutions how to get around the poor design, but the embedded functions will make the query run much slower than if you were able to extract the information with a simple JOIN.