Need help with SELECT statement

[Tony Girgenti]

I tried this select statement:
CusQry = "SELECT DISTINCT C.*, L.item_no " & _
"FROM cust AS C " & _
"JOIN sa_hdr AS H " & _
"ON C.nbr = H.cust_no " & _
"JOIN sa_lin AS L " & _
"ON H.ticket_no = L.hdr_ticket_no " & _
"WHERE L.item_no BETWEEN @StartItem AND @EndItem " & _
"AND H.post_dat BETWEEN @StartDate AND @EndDate " & _
"AND C.zip_cod BETWEEN @StartZipCode AND @EndZipCode " & _
"AND C.cat = @CustCat;"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

When I execute the "SET", I get the error "Error 3131 (Syntax error in FROM
clause)"
I'm doing this in Microsoft Access 2000 using VBA/DAO programming on linked
tables through ODBC. The tables were populated using COBOL programs and
Pervasive.SQL 2000 file system.

cust is the customer table
sa_hdr is the order header table
sa_lin is the order lines table
@StartItem, @EndItem, @StartDate, @EndDate, @StartZipCode, @EndZipCode &
@CustCat are all variables in my program defined as STRING.

Any help that can be provided would be greatly appreciated.

Thanks,
Tony

 

[Michel Walsh]

Hi,


Change JOIN by INNER JOIN


If that still does not work, try adding the ( ) as follow

... FROM ( cust As C INNER JOIN sa_hdr As H ON C.nbr=H.cust_no) INNER
JOIN sa_lin As L ON ...



Hoping it may help,
Vanderghast, Access MVP