SQL statement in lookup field returns wrong result to field

[crush]

Hi,

I have a lookup field that opens a form that displays results from the following SQL statement:

Code: Select all

SELECT * FROM product_points WHERE pp_code LIKE 'AC%' OR pp_code LIKE 'BD%' GROUP BY pp_code

But, no matter which row you select, it always returns the first one to the form. Now, when I change the OR to an AND

Code: Select all

SELECT * FROM product_points WHERE pp_code LIKE 'AC%' AND pp_code LIKE 'BD%' GROUP BY pp_code

it works fine.

Keeping the OR operator, I tried changing the field and the criteria for the second LIKE operator, not like that should make a difference, and not surprise, it didn't.

Any idea what's going on? Or what sort of workaround I could use?

Thanks,
Cassidy

[admin]

Cassidy,

When you say "opens a form" do you mean "opens a Browse (Lookup) Form"?

If so, when you pick a row from this list, you're saying that your choice isn't always the one that repopulates the Lookup Object?

What Primary Key is set on this Lookup Form that is being opened?

Steven

(Maybe some screen captures will help me understand.)

[painatal]

To solve this problem add a parenthisis to your WHERE statement.

Nubuilder adds something like this to the end of a query when using a lookup:

<ORIGINAL QUERY> (added by nubuilder) "AND id=stuff".

So when using OR the precendences of the operations is not like you are wanting and nuBuilder returns all the lines thus only the first is shown and appears as selected.

Hope this helps

[crush]

Thank you Steven and painatal for your answers.

I took painatal's suggestions, and found that using

Code: Select all

SELECT * FROM do_product_points WHERE (pp_code LIKE '%AC%' OR pp_code LIKE '%BD%') GROUP BY pp_code

did the trick!

[admin]

.