-
Aug 31st, 2017, 05:09 AM
#1
Thread Starter
Fanatic Member
Query problems
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
-
Aug 31st, 2017, 05:26 AM
#2
Re: Query problems
Does it give an error?
What are the table definitions?
Is this an Access database or something else?
Code:
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 transport.id Desc;
-
Aug 31st, 2017, 05:26 AM
#3
Re: Query problems
Yes it is a syntax error
At least I see one missing parenthesis
This
or approved=false order by 1 desc
Should be
or approved=false) order by 1 desc
That closing paren is needed obviously since you OPEN'ed one after the "where"
-
Aug 31st, 2017, 07:39 AM
#4
Thread Starter
Fanatic Member
Re: Query problems
The program takes issue with the reference to these names:
InStr(UCase(fromplaces.pickupplace), 'BLO') > 0 Or InStr(UCase(toplaces.deliverplace), 'BLO') > 0
There are no syntax errors in my first published query
-
Aug 31st, 2017, 08:31 AM
#5
Re: Query problems
Nothing wrong with the parens.
You can't use column aliases in the WHERE clause since it is processed before the SELECT. See here for one reference.
-
Aug 31st, 2017, 08:55 AM
#6
Thread Starter
Fanatic Member
Re: Query problems
Thanks, topshot,
It got it write by using the alias table names and the actual field names.
-
Aug 31st, 2017, 09:44 AM
#7
Re: Query problems
Juggalo
Your reconfiguration of PK's statement is brilliant ..
No one here thought to do that
http://www.vbforums.com/showthread.p...n-Access-Query
Spoo
-
Aug 31st, 2017, 10:25 AM
#8
Re: Query problems
Spoo - that thread is this thread before it got "moved"....
but....
I wonder... what's the difference in performance of InStr(UCase(toplaces.deliverplace), 'BLO') > 0 VS using a LIKE, as in: toplaces.deliverplace LIKE '*BLO*' ... LIKE should also be case insensitive too, shouldn't it? Or is that dependent on the collation of the database/field (like it is in SQL Server)?
-tg
-
Aug 31st, 2017, 10:55 AM
#9
Re: Query problems
TG
that thread is this thread before it got "moved"....
Yes .. emphasis on the quotation marks
As of now, it's still there.
Spoo
-
Sep 5th, 2017, 08:46 AM
#10
Re: Query problems
Originally Posted by Spooman
How is it brilliant? It's the standard way to view a sql query, Sql Management Studio does that formatting for you.
Originally Posted by techgnome
Spoo - that thread is this thread before it got "moved"....
but....
I wonder... what's the difference in performance of InStr(UCase(toplaces.deliverplace), 'BLO') > 0 VS using a LIKE, as in: toplaces.deliverplace LIKE '*BLO*' ... LIKE should also be case insensitive too, shouldn't it? Or is that dependent on the collation of the database/field (like it is in SQL Server)?
-tg
I would be inclined to think using a Like would be far more efficient than a dual function call (InStr & UCase), though if he's only got a handful of records he's working with it probably wouldn't make much of a difference, but record sets exceeding 1 million would probably give a noticeable performance difference.
-
Sep 5th, 2017, 08:57 AM
#11
Re: Query problems
Originally Posted by JuggaloBrotha
How is it brilliant? It's the standard way to view a sql query, Sql Management Studio does that formatting for you.
Not being familiar with that, I was initially comparing your "indented" multi-line Select .. From .. Where statement to OP's rather lengthy 1-line presentation. "Yours" is much easier to read.
OK, if Sql Management Studio does all that automatically, I hereby retract the "brilliant" characterization ,,
Spoo
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|