Results 1 to 11 of 11

Thread: Query problems

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    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

  2. #2
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

    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;
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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"

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    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

  5. #5
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    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.

  6. #6

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Query problems

    Thanks, topshot,

    It got it write by using the alias table names and the actual field names.

  7. #7
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  10. #10
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

    Re: Query problems

    Quote Originally Posted by Spooman View Post
    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
    How is it brilliant? It's the standard way to view a sql query, Sql Management Studio does that formatting for you.
    Quote Originally Posted by techgnome View Post
    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.
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  11. #11
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Query problems

    Quote Originally Posted by JuggaloBrotha View Post
    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
  •  



Click Here to Expand Forum to Full Width