Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Unanswered: MS Acces Query Help needed

    Can anybody tell me whats wrong with my MsAcces SQL given below.The error says- Syntax error[missing operator] in where clause
    Code:
    SELECT
         SalesInvoice.`SalesDate` AS SalesInvoice_SalesDate,
         SalesInvoice.`ID` AS SalesInvoice_ID,
         UniteSalse.`ID` AS UniteSalse_ID,
         Unit.`Garage` AS Unit_Garage,
         SalesInvoice.`TotalAmount` AS SalesInvoice_TotalAmount,
         SalesInvoice.`Commission` AS SalesInvoice_Commission,
         SalesInvoice.`Paid` AS SalesInvoice_Paid,
         SalesInvoice.`Due` AS SalesInvoice_Due
    FROM
         `SalesInvoice` SalesInvoice,
         `UniteSalse` UniteSalse,
         `Unit` Unit
    WHERE SalesInvoice.ID = UniteSalse.SalesInvoiceID
    AND UniteSalse.ID = Unit.ID
    AND SalesInvoice.SalesDate BEWEEN #1/2/2008 AND #1/2/2008#

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    IM guessing its the date banding...
    AND SalesInvoice.SalesDate BEWEEN #1/2/2008 AND #1/2/2008#
    should read
    AND SalesInvoice.SalesDate BEWEEN #1/2/2008# AND #1/2/2008#

    I forget if the date banding should be quoted eg
    "#1/2/2008#" AND "#1/2/2008#"

    I'd want to check if the table unitesalse was called unitesalse and not say unitsales

    Ive not used the where construct to join tables in years
    I'd suggest you use the join construct in place, there's nothing intrinsically wrong with a where (theta) join except it is deprecated and the join construct is far more powerful

    ...
    FROM SalesInvoice
    join UniteSalse on UniteSalse.UniteSalse.SalesInvoiceID =SalesInvoice.ID
    join Unit on Unit.ID = UniteSalse.ID
    where.....
    I don't think you need to quote column names
    eg salesinvoice.'salesdate'
    in Acess/ JET the encapsualtion charcter is []
    eg
    [salesinvoice].[salesdate]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I wonder how JET will process those backticks too.

    The delimitation of dates is not in quotes... ie #1/2/2008# not "#1/2/2008#".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Was about to write re ANSI JOIN syntax but I see you got there first Mark. BTW - it isn't a THETA join - it is a CROSS join. A theta join is using an inequality operator (like > or < or, very rarely, <>).

    All the backticks need to go.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you do this in access??? --

    FROM SalesInvoice
    join UniteSalse on UniteSalse.UniteSalse.SalesInvoiceID =SalesInvoice.ID
    join Unit on Unit.ID = UniteSalse.ID

    i've only got access 97 but i understand you still need to parenthesize multiple joins in later editions --

    FROM ( SalesInvoice
    join UniteSalse on UniteSalse.UniteSalse.SalesInvoiceID =SalesInvoice.ID )
    join Unit on Unit.ID = UniteSalse.ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My understanding is you only need parenth...pare....par....brackets if you have 3+ tables. That's why all my JET queries include 2 or less tables

    EDIT - in short - No.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2 or fewer

    thanks for the confirmation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My most wretched grovelling to you and Robert Baker.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    ... and Robert Baker.
    Robert Baker ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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