Still have this problem with field on report

[hog]

The sql below returns all required fields that I want on my report. The report fields reference each field as tblContracts.pr_number etc and if I use this sql in a dataadapter it returns the data I'd expect.

 

Problem is if I put all these fields on my report I get an error from the crystal reports engine. If I remove the suppliername field from the report it works OK. This field is the only one which comes from another table, but as the data all gets put into a dataset into a table called tblContracts it uses the prefix tblContracts like all the others.

 

Any ideas please as this is driving me nuts!!

 

       m_strSQL = "SELECT tblContracts.order_line, tblContracts.pr_number, tblContracts.budget, tblContracts.dept, " & _
                  "tblContracts.po_number, tblContracts.dated, tblContracts.commencement, tblContracts.expires, " & _
                  "tblContracts.contact, tblContracts.contact_tel, tblContracts.price, tblContracts.description, " & _
                  "tblContracts.active, tblContracts.internal, tblContracts.s0, tblContracts.s1, tblContracts.s2, " & _
                  "tblContracts.s3, tblContracts.s4, tblContracts.s5, tblContracts.s6, tblContracts.s7, tblContracts.s8, " & _
                  "tblContracts.s9, tblContracts.s10, tblContracts.s11, tblSuppliers.name AS suppliername FROM tblContracts INNER JOIN " & _
                  "tblSuppliers ON tblContracts.Supplierid = tblSuppliers.supplierid WHERE (tblContracts.po_number = '" & _
                  strPONumber & "') ORDER BY order_line "

[hog]

Sorted this...

 

It helps if when calling the report I pass the correct dataset.....DOH.

 

Still the investigative work looking into this has given me new insight into VB and crystal reports