SQL Getting values from 3 different tables

[metalheadstorm]

Ok this is starting to hurt my head :)

I have a Invoice table that has Invoice_Id , Customer_Id and Product_Code

Invoice_Id is the primary key
Customer_Id is a foreign key
Product_Code is a foreign key

so im making a data report and i can get it to work with connecting an retrieving data from the invoice and customer table but when i try and get data from the product table it fails

this is what i have atm

InvoiceSQL = "SELECT * From Invoice INNER JOIN Customer ON Customer.Custom er_Id = Invoice.Custome r_Id WHERE Invoice.Invoice _ID=" & Txt_InvoiceID.T ext & " INNER JOIN Product ON Invoice.Product _Code = Product.Product _Code WHERE Invoice.Product _Code =" & Txt_InvoiceProd Code.Text & ""

can anyone help ;)

btw im using vb6 and MS Access

[metalheadstorm]

I was looking back and i dontthink i need the

WHERE Invoice.Product _Code =" & Txt_InvoiceProd Code.Text & ""

at the end

[metalheadstorm]

Still need help though :) cant go any further without sorting this ^^

sorry for being impatient

[Killer42]

Still need help though :) cant go any further without sorting this ^^

sorry for being impatient

Sorry, you're too impatient - I'm going to ban you for life... :D

Seriously, I'm going to move this thread to the Access forum, as you'll find much more SQL expertise there.

I believe it would help if you could copy and paste here the actual contents of the string passed to the SQL interpret (in other words, the srting built by the code you showed us).

And also, could you give a more detailed explanation of what "fail" means in this context? For all we know it might have retrieved the wrong data, retrieved nothing, returned an error, blown up the PC...

[Delerna]

OK
You can't have 2 different where clauses in the same query like that.

change it to something like this

SELECT * 
FROM Invoice 
INNER JOIN Customer ON Customer.Customer_Id = Invoice.Customer_Id 
INNER JOIN Product ON Invoice.Product_Code = Product.Product_Code 
WHERE Invoice.Invoice_ID=" & Txt_InvoiceID.Text & " 
       and Invoice.Product_Code =" & Txt_InvoiceProdCode.Text & ""

[metalheadstorm]

Hmm i seem to get this error


syntax error(missing Operator) in query expression 'Customer.Custo mer_Id = Invoice.Custome r_Id INNER JOIN Product ON Invoice.Product _Code = Product.Product _Code'.

[metalheadstorm]

Anyone got any ideas ?: D

[Delerna]

not from that ??
Are you sill using a dynamic sql string?
Have you tried SELECT @SQLString
and examined the contents to see if the query was constructed correctly?

[Scott Price]

Metalheadstorm,

Can you give us the SQL you are using now? Also, what version of Access are you working with?

You seem to be confusing the purpose of the WHERE clause, judging from your first posted sql statement. The WHERE clause does not provide criteria for the join, it provides criteria for the selection of the data.

Regards,
Scott

[Scott Price]

You're going to end up with something looking like this:

SELECT INVOICE.*, INVOICE.Invoice ID, *
FROM (Customer INNER JOIN Invoice ON Customer.Custom erID = Invoice.Custome rID) INNER JOIN Product ON Invoice.Product ID = Product.Product ID
WHERE (((Invoice.Invo iceID)=[Form1].[Text1]));


Regards,
Scott

[metalheadstorm]

Oooo sorry i didnt see ther was some more replies, thats why i havent posted back yet , i will try that scot thx and try and sort this problem out :P