Run-time Error 107 / VBA SQL Query Drama

[Akart]

Hello,

I have a module that creates an SQL query and all works fine except when one particular table is involved (financiallineitems). As follows:

I have 4 tables.

Customers
| - Related by "CustomerID"
Invoices
/ \-Both Related by "InvoiceID"
ProductLineItems FinancialLineItems


When i do this query. It works and a record is displayed where the Productlineitems.returnno = '23'

SELECT DISTINCT Customers_AU.CustomerID, Customers_AU.Title, Customers_AU.Name, Customers_AU.Surname, Customers_AU.BillingSuburb, Customers_AU.Phone1, Invoices_AU.InvoiceID FROM Invoices_AU INNER JOIN Customers_AU ON Invoices_AU.CustomerID = Customers_AU.CustomerID INNER JOIN ProductLineItems_AU ON Invoices_AU.InvoiceID = ProductLineItems_AU.InvoiceID WHERE ProductLineItems_AU.ReturnNo = '23'

When i do the following query i get an error

"Run-time
error '107':

The Column prefix 'financiallineitems' does not match with a table name or alias name used in the query."

The query sent was:

SELECT DISTINCT Customers_AU.CustomerID, Customers_AU.Title, Customers_AU.Name, Customers_AU.Surname, Customers_AU.BillingSuburb, Customers_AU.Phone1, Invoices_AU.InvoiceID FROM Invoices_AU INNER JOIN Customers_AU ON Invoices_AU.CustomerID = Customers_AU.CustomerID INNER JOIN FinancialLineItems_AU ON Invoices_AU.InvoiceID = FinancialLineItems_AU.InvoiceID WHERE financiallineitems.RunNumberFinancial = '23'

This is supposed to return the records that financiallineitems.runnnumberfinancial = '23'

Judging from the error, it means that i did not include an INNER JOIN to Financial line items but i have...

I have tried changing financiallineitems to FinancialLineItems to no avail.
Should this be an OUTER JOIN or something....

Help me please!!!

Regards,

Akart!

 

[Akart]

Aaaargh wish i could delete posts... This was another stupid mistake i made and noone could have helped me because i did not give enough information on the post (I tried =)

anyway, the table name was FinancialLineItems_AU but my code was creating the sql statement without the _AU in it.... Fixed now CYA>

Akart