Searching text in Access Query

[Peekay]

I have done a query without search which worked fine, but as soon as I add the search criteria it does not work anymore. Can somebody please help me. It is not a syntax error:

Select transport.id, enterdate, transporttypes.typename, itemid, itemname, refno, unit, quantity, fromplaces.placename as pickupplace, toplaces.placename as deliverplace, distance, weight, rate, quantity*rate as amount, deliverdate, touser, transport.approved, transport.approvedby, datedelivered, invno, transport.closed, transport.enteredby from (((transport left join transporttypes on transporttypes.id=transport.transporttype) left join places as fromplaces on fromplaces.id=transport.pickupat) left join places as toplaces on toplaces.id=transport.deliverto) where (InStr(UCase(transport.itemname),'BLO')> 0 or InStr(UCase(transport.refno),'BLO')> 0 or InStr(UCase(fromplaces.pickupplace),'BLO')> 0 or InStr(UCase(toplaces.deliverplace),'BLO')> 0) or approved=false order by 1 desc

Thanks
PK

[Spooman]

Peekay

If no syntax error, you've got to admit that it is rather "complex"

Why not try a very simple SELECT ,, WHERE statement and see what happens.

Spoo

[SamOscarBrown]

This Thread really belongs in the Database Section of this forum...it is not VB6 directly related. However, the advice from spoo is a good one. Your query is multi-faceted. Any one of those criteria, if not met, will, of course, result in an incorrect return. What I would suggest is that you put this query into a variable, then before execution, walk through the debugger (or simply send the variable to a debug statement) and see what is actually being sent to your database.

The other thing you can do is copy this into Access, run the query, and see if it works directly there...I doubt it will.

[DataMiser]

It would help to know what 'not working' means
Also the one think that jumps out at me is 'BLO' Is that correct? Should it maybe be 'BOL' ?

[Peekay]

I have transferred the thread to the Database forum. Thanks guys.