Syntax error 3075 in VBA/SQL involving two LEFT JOINS

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
 
E

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
 
M

Michel Walsh

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...
 
E

EagleOne

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?
 
E

EagleOne

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...
 
M

Michel Walsh

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error 8
SQL Syntax error in VBA/Access 0
Reference SQL in Access VB 5
runtime error 3075 in query expression 3
Using an Append query in VBA to change the field name provides theSyntax Error 3075 2
SQL Query in VBA 9
Setting SQL for Pass through qry results in a 3075 error 4

Top