Instr Issue - Newbie really needs help!!

[thorwood]

Hi

I am new to this forum, and relatively new to Access in general. I'm wondering if anyone can offer advice on the following....

I have a table "LOCATIONS" with the following information in:

LOCS Description
WORLD everyone
WORLDXUSA all except USA
OTHER other countries

I have another table "RULES" with the following information (for example) in:

RULE Areas of the globe
1 X(#WORLD)
2 #OTHER,#WORLD
3 #WORLDXUSA

I have an instring query which looks for any of the [LOCATIONS.LOCS] within [RULES.Areas of the globe].

For Rule 1, its fine, I get "WORLD" because the string is within "RULES"

For Rule 2, its fine, I get "OTHER" and I also get "WORLD"

For Rule 3, though, I get "WORLDXUSA" as expected, but I also get "WORLD" because its found within it, and I don't want to find it in this instance.

The query I'm using to obtain the above is:

InStr([RULES].[Areas of the globe],[LOCATIONS].[LOCS])
where <> "0"

Does anyone know how I can get this result to exclude "WORLD" (well, not specifically world but any one of the fields which exist on their own but could be found within a valid one) if it finds "WORLDXUSA" within a string such as "WORLDXUSA" or "(WORLDXUSA)" or "WORLDXUSA,OTHER", etc? Remember, I might want to legitimately find "WORLD" elsewhere....

Many thanks in advance

Tim

 

[Golom]

InStr isn't the best way of doing this. You should look at the LIKE keyword and pattern matching but you can make Instr work for you

InStr([RULES].[Areas of the globe] & ",", [LOCATIONS].[LOCS] & ",")

If you append a comma to each of the arguments then it is searching for "WORLD[red],[/red]" rather than just "WORLD" and "WORLDXUSA" won't match that.