Query oddity help

TCovert

I have this query, it works fine if "and atexid like '%'" is not in the string. But when I add that part, it returns no records.

What am I doing wrong?

select * from orderentry where orderid like '%%' and acctid like '%%' and acctname like '%oreck%' and atexid like '%' order by orderid

LordShryku

Okay, why all the blank like statements anyway?

rachel2004

Originally posted by TCovert

What am I doing wrong?

atexid like '%' order by orderid

All the % symbol means is match zero or more characters when used in conjunction with the LIKE clause. So, you query is saying to find all of these matches WHERE... AND... AND... AND... but on your last AND, you are saying to match zero or more of nothing, which of course will give no result set if all of your other columns contain data but atexid does not.

Can you insert a field name after your % symbol and try again rather than just have the % symbol alone in that last AND?

Post back with your results.

TCovert

The query actualy looks like this:

select * from orderentry where orderid like '%$orderid%' and acctid like '%$acctno%' and acctname like '%$acctname%' and atexid like '%$atexid%' order by orderid

So, depending on what field they search by, it will find the record.

rachel2004

Originally posted by TCovert
The query actualy looks like this:

select * from orderentry where orderid like '%$orderid%' and acctid like '%$acctno%' and acctname like '%$acctname%' and atexid like '%$atexid%' order by orderid

Thanks, I could've just saved myself some typing had you posted that line to start with.

TCovert

Well, nevermind, I fixed it. I don't know what happened, but I deleted this field from the DB and re-added it and now it works.

If anyone has any explanation, I would like to know.

Thanks.