VB / Access / SQL syntax problem

[Tony K]

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.customer_id, address.address1, address.address2,
address.city, address.state, address.postal_code, address.country,
address.record_status,
customer.record_status AS Expr1,
customer.company_id, customer.customer_number, customer.name,
customer.company_name, address.address3,
address.address4
FROM (address INNER JOIN
customer ON address.address_id =
customer.customer_id AND address.record_type = 'Customer Shipping')
WHERE (customer.customer_id > 0)



or



SELECT customer.customer_id, address.address1, address.address2,
address.city, address.state, address.postal_code, address.country,
address.record_status,
customer.record_status AS Expr1,
customer.company_id
FROM (address INNER JOIN
customer ON address.address_id =
customer.customer_id AND address.record_type = 'Customer Mailing')
WHERE (customer.customer_id > 0)


Both state the JOIN expression is not supported

Any help would be greatly appreciated.

Thanks,
Tony K

 

[George Shubin]

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'

 

[Cor Ligthert[MVP]]

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