VB / Access / SQL syntax problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tony K
    #1

    VB / Access / SQL syntax problem

    I'm slowly converting views from an SQL DB to Jet (Access) and I'm having
    some problems with the syntax. Can anyone tell me what the problem is with
    the syntax of the following SQL statements for Access?

    SELECT customer.custom er_id, address.address 1, address.address 2,
    address.city, address.state, address.postal_ code, address.country ,
    address.record_ status,
    customer.record _status AS Expr1,
    customer.compan y_id, customer.custom er_number, customer.name,
    customer.compan y_name, address.address 3,
    address.address 4
    FROM (address INNER JOIN
    customer ON address.address _id =
    customer.custom er_id AND address.record_ type = 'Customer Shipping')
    WHERE (customer.custo mer_id 0)



    or



    SELECT customer.custom er_id, address.address 1, address.address 2,
    address.city, address.state, address.postal_ code, address.country ,
    address.record_ status,
    customer.record _status AS Expr1,
    customer.compan y_id
    FROM (address INNER JOIN
    customer ON address.address _id =
    customer.custom er_id AND address.record_ type = 'Customer Mailing')
    WHERE (customer.custo mer_id 0)


    Both state the JOIN expression is not supported

    Any help would be greatly appreciated.

    Thanks,
    Tony K

    Tags: None
    • George Shubin
      #2
      Re: VB / Access / SQL syntax problem

      Take the

      AND address.record_ type = 'Customer Shipping' out of the JOIN and put it in
      the WHERE.

      Same for

      AND address.record_ type = 'Customer Mailing'


      "Tony K" <notmyfirstname @kingprogrammin g.comwrote in message
      news:2CCD7961-7960-464D-A9A3-00F6CF8B8438@mi crosoft.com...
      I'm slowly converting views from an SQL DB to Jet (Access) and I'm having
      some problems with the syntax. Can anyone tell me what the problem is
      with the syntax of the following SQL statements for Access?
      >
      SELECT customer.custom er_id, address.address 1, address.address 2,
      address.city, address.state, address.postal_ code, address.country ,
      address.record_ status,
      customer.record _status AS Expr1,
      customer.compan y_id, customer.custom er_number, customer.name,
      customer.compan y_name, address.address 3,
      address.address 4
      FROM (address INNER JOIN
      customer ON address.address _id =
      customer.custom er_id AND address.record_ type = 'Customer Shipping')
      WHERE (customer.custo mer_id 0)
      >
      >
      >
      or
      >
      >
      >
      SELECT customer.custom er_id, address.address 1, address.address 2,
      address.city, address.state, address.postal_ code, address.country ,
      address.record_ status,
      customer.record _status AS Expr1,
      customer.compan y_id
      FROM (address INNER JOIN
      customer ON address.address _id =
      customer.custom er_id AND address.record_ type = 'Customer Mailing')
      WHERE (customer.custo mer_id 0)
      >
      >
      Both state the JOIN expression is not supported
      >
      Any help would be greatly appreciated.
      >
      Thanks,
      Tony K

        Comment

        • Cor Ligthert[MVP]
          #3
          Re: VB / Access / SQL syntax problem

          Tony,

          Are you sure you are not generating this with in the same time generating
          code for the update, insert and delete.

          You cannot generate update, insert and delete code while it is about a
          joined select.

          Did you try it with the boxes for that feature unselected?

          Cor

          "Tony K" <notmyfirstname @kingprogrammin g.comschreef in bericht
          news:2CCD7961-7960-464D-A9A3-00F6CF8B8438@mi crosoft.com...
          I'm slowly converting views from an SQL DB to Jet (Access) and I'm having
          some problems with the syntax. Can anyone tell me what the problem is
          with the syntax of the following SQL statements for Access?
          >
          SELECT customer.custom er_id, address.address 1, address.address 2,
          address.city, address.state, address.postal_ code, address.country ,
          address.record_ status,
          customer.record _status AS Expr1,
          customer.compan y_id, customer.custom er_number, customer.name,
          customer.compan y_name, address.address 3,
          address.address 4
          FROM (address INNER JOIN
          customer ON address.address _id =
          customer.custom er_id AND address.record_ type = 'Customer Shipping')
          WHERE (customer.custo mer_id 0)
          >
          >
          >
          or
          >
          >
          >
          SELECT customer.custom er_id, address.address 1, address.address 2,
          address.city, address.state, address.postal_ code, address.country ,
          address.record_ status,
          customer.record _status AS Expr1,
          customer.compan y_id
          FROM (address INNER JOIN
          customer ON address.address _id =
          customer.custom er_id AND address.record_ type = 'Customer Mailing')
          WHERE (customer.custo mer_id 0)
          >
          >
          Both state the JOIN expression is not supported
          >
          Any help would be greatly appreciated.
          >
          Thanks,
          Tony K

            Comment

            Previous template Next
            Working...