Need help with SELECT statement

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

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
 
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
 
Back
Top