Syntax error 3075 in VBA/SQL involving two LEFT JOINS

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003, 2007

I am getting a 3075 error (Missing Operator) with the following:

strSQL = "SELECT * INTO ContraDetailTbl FROM TransactionsTbl LEFT JOIN [TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbers].AccountNumber = [TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL

My goal is to capture in ContraDetailTbl all Transactions which have an Accountnumber =
AccountNumbers.AccountNumber AND TranNum = TransNmbrInAcctTbl.TranNum.

Any thoughts?

TIA EagleOne
 
UPDATE (This is the corrected/consistent code which still errors)

strSQL = "SELECT * INTO ContraDetail FROM TransactionsTbl LEFT JOIN [TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber = [TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL
 
Try:


strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum ) LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




Why do you use left join if your where clause is, logically, remove their
effect? Why not using INNER JOIN ?



Vanderghast, Access MVP



UPDATE (This is the corrected/consistent code which still errors)

strSQL = "SELECT * INTO ContraDetail FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




2003, 2007

I am getting a 3075 error (Missing Operator) with the following:

strSQL = "SELECT * INTO ContraDetailTbl FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbers].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL

My goal is to capture in ContraDetailTbl all Transactions which have an
Accountnumber =
AccountNumbers.AccountNumber AND TranNum = TransNmbrInAcctTbl.TranNum.

Any thoughts?

TIA EagleOne
Click to expand...
Click to expand...
 
Michel,

Thanks for your time & knowledge.

I am still getting 3075. That said, the text of the error has changed now reading:

((([TransNmbrInAcctTbl].TranNum) Is Not Null)) AND WHERE ((([AccountNumbersTbl.AccountNumber] Is
Not Null))

Notice that the preceeding "WHERE" was not included in the error text. Therefore I am not sure of
my use of "AND WHERE."

Would you please edit my code to include the suggestion to use an INNER JOIN?
 
BTW I did get the following to work:

strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl LEFT JOIN [TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum ) LEFT JOIN" _
& " [AccountNumbersTbl] ON [AccountNumbersTbl].AccountNumber =" _
& " [TransNmbrInAcctTbl].AccountNumber WHERE (((([TransNmbrInAcctTbl].TranNum) Is Not" _
& " Null)) AND ((([AccountNumbersTbl].AccountNumber) Is Not Null)));"

That said, I am very interested in your example of using and INNER JOIN.

TIA EagleOne

Michel Walsh said:
Try:


strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum ) LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




Why do you use left join if your where clause is, logically, remove their
effect? Why not using INNER JOIN ?



Vanderghast, Access MVP



UPDATE (This is the corrected/consistent code which still errors)

strSQL = "SELECT * INTO ContraDetail FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




2003, 2007

I am getting a 3075 error (Missing Operator) with the following:

strSQL = "SELECT * INTO ContraDetailTbl FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbers].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL

My goal is to capture in ContraDetailTbl all Transactions which have an
Accountnumber =
AccountNumbers.AccountNumber AND TranNum = TransNmbrInAcctTbl.TranNum.

Any thoughts?

TIA EagleOne
Click to expand...
Click to expand...
Click to expand...
 
yep, it is not ... WHERE ... AND WHERE ... The second "where" is to be
removed.


The inner join would be:



strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl INNER JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum ) INNER
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber; "
DoCmd.RunSQL strSQL





without any where clause.



Vanderghast, Access MVP

BTW I did get the following to work:

strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum )
LEFT JOIN" _
& " [AccountNumbersTbl] ON [AccountNumbersTbl].AccountNumber =" _
& " [TransNmbrInAcctTbl].AccountNumber WHERE
(((([TransNmbrInAcctTbl].TranNum) Is Not" _
& " Null)) AND ((([AccountNumbersTbl].AccountNumber) Is Not
Null)));"

That said, I am very interested in your example of using and INNER JOIN.

TIA EagleOne

Michel Walsh said:
Try:


strSQL = "SELECT * INTO ContraDetail FROM ( TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum ) LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




Why do you use left join if your where clause is, logically, remove their
effect? Why not using INNER JOIN ?



Vanderghast, Access MVP



UPDATE (This is the corrected/consistent code which still errors)

strSQL = "SELECT * INTO ContraDetail FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbersTbl].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL




(e-mail address removed) wrote:

2003, 2007

I am getting a 3075 error (Missing Operator) with the following:

strSQL = "SELECT * INTO ContraDetailTbl FROM TransactionsTbl LEFT JOIN
[TransNmbrInAcctTbl]" _
& " ON [TransactionsTbl].TranNum = [TransNmbrInAcctTbl].TranNum LEFT
JOIN [AccountNumbersTbl]" _
& " ON [AccountNumbers].AccountNumber =
[TransNmbrInAcctTbl].AccountNumber " _
& " WHERE ((([TransNmbrInAcctTbl].TranNum) Is Not Null))" _
& " AND WHERE ((([AccountNumbersTbl.AccountNumber] Is Not Null));"
DoCmd.RunSQL strSQL

My goal is to capture in ContraDetailTbl all Transactions which have an
Accountnumber =
AccountNumbers.AccountNumber AND TranNum = TransNmbrInAcctTbl.TranNum.

Any thoughts?

TIA EagleOne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top