Results 1 to 6 of 6

Thread: Help with SQL

  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Help with SQL

    Hi can anybody help
    I am trying to retrieve data based on a specific code andonly before a specific date for instance AZ before 17 - mar - 2012, however I have 10 different codes in the same field and 10 different dates.
    If I put the following in it returns results

    [ Sqle = Sqle & " and b.shipcode = 'VE' and b.departuredate < '10-Mar-2012'" ]

    However when I create this it does not return any values


    [ Sqle = Sqle & " and b.shipcode = 'VE' and b.departuredate < '10-Mar-2012'"
    Sqle = Sqle & " and b.shipcode = 'AZ' and b.departuredate < '17-Mar-2012'" ]


    I know there is probably a very simple solution but I am just not seeing it

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What kind of syntax is that? It is not SQL. Does the [...] denote an OR-condition? And what's the double-quote stuff and the & doing?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    sql help

    The square brackets are not part of the code, this is part of an sql statement created in visual basic

    Sqle = Sqle & " and b.shipcode like 'VE' and b.departuredate < '10-Mar-2012'"
    Sqle = Sqle & " Order by b.openedby, b.opendate"

    The double quotes are automatically applied by VB, but i basically want all results that have VE if depart before 10 march as well as all that have az if depart before 17 march and so on

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you dont' say what SQL engine you are using...
    if thats JET / Access then your dates need to be encapsulated by # and use the US date format mm/dd/yyyy or the iso date format yyyy/mm/dd
    eg
    Code:
    WHERE mydatecolumn < '#03/10/2012#'
    ..any error messages?
    .. im not used to seeing VB statements enclosed by [ & ].. is that valid VB syntax. if its to denote the sql in your posting then prefix with [ c o d e ] and suffix with [ / c o d e ]
    ..natch remove the spaces before use
    just a though have you actually checked there is data that matches those criteria?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm sticking to SQL because this forum is about ISO/ANSI SQL and not some other syntax used by some products...

    If you want to have all records "VE if depart before 10 march as well as all that have az if depart before 17 march", you'd have a query like this:
    Code:
    SELECT *
    FROM   ...
    WHERE  ( b.shipcode = 'VE' AND
              b.departuredate < '10-Mar-2012' ) OR
           ( b.shipcode = 'AZ' AND
              b.departuredate < '17-Mar-2012' )
    The way you wrote it above:
    Code:
    [ Sqle = Sqle & " and b.shipcode = 'VE' and b.departuredate < '10-Mar-2012'"
    Sqle = Sqle & " and b.shipcode = 'AZ' and b.departuredate < '17-Mar-2012'" ]
    cannot work because you have two predicates "b.shipcode = 'VE' AND b.shipcode = 'AZ'" which are mutually exclusive - if one would evaluate to true for a row, the other would evaluate to false so that the ANDing of both predicates is always false.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by healdem View Post
    Code:
    WHERE mydatecolumn < '#03/10/2012#'
    Yes, except that the pound signs replace the single quotes:
    Code:
    WHERE mydatecolumn < #03/10/2012#
    im not used to seeing VB statements enclosed by [ & ]
    The '&' is VBA's string concatenation operater -- the OP's building up a string of SQL. I think the open and close brackets are the OP's substitute for using '[code]' tags, which would be more appropriate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •