Syntax error in FROM clause

[billyhs]

Dear experts,

I'm using VB6 and Access database for my development. Currently, I'm having a problem with SQL syntax. VB6 gave me an error message saying "Syntax error in FROM clause" which I have been trying to fix but had no luck so far.
Here is the code portion which contains the SQL statements:

.Source = "SELECT trpm.IDTipeKemasan, trpm.ShiftKerja, ml.Location, " & _
"nm.NamaMesin, tm.TipeMesin, jp.JenisProduk, " & _
"tk.TipeKemasan, dp.NamaProduk, ds.Nama " & _
"FROM (((((((TempRekapProdMesin trpm " & _
"INNER JOIN TipeKemasan tk ON trpm.IDTipeKemasan = tk.IDTipeKemasan) " & _
"INNER JOIN MachineLocation ml ON trpm.LokasiMesin = ml.IDLoc) " & _
"INNER JOIN DataSupervisor ds ON trpm.IDSupervisor = ds.IDSupervisor) " & _
"INNER JOIN DataProduk dp ON trpm.IDProduk = dp.IDProduk) " & _
"INNER JOIN NamaMesin nm ON trpm.IDNamaMesin = nm.IDNamaMesin) " & _
"INNER JOIN TipeMesin tm ON trpm.IDTipeMesin = tm.IDTipeMesin) " & _
"INNER JOIN JenisProduk jp ON trpm.IDJenisProduk = jp.IDJenisProduk) " & _
"JOIN " & _
"(SELECT trpm2.IDTipeKemasan, SUM(trpm2.JumlahMesin) AS tJumlahMesin, SUM(trpm2.JamRencana) AS tJamRencana, " & _
"SUM(trpm2.JamRealisasi) AS tJamRealisasi, SUM(trpm2.TolakUkur) AS tTolakUkur, SUM(trpm2.HasilProduksi) AS tHasilProduksi, " & _
"SUM(trpm2.DownTimeProduksi) AS tDownTimeProduksi, SUM(trpm2.DownTimeMesin) AS tDownTimeMesin, SUM(trpm2.LostTime) AS tLostTime, " & _
"SUM(trpm2.JumlahPekerja) AS tJumlahPekerja, " & _
"SUM(trpm2.Pengemasan) AS tPengemasan " & _
"FROM TempRekapProdMesin trpm2 " & _
"WHERE trpm2.IDPemakai = '" & sIDPemakai & "' " & _
"GROUP BY trpm2.IDTipeKemasan) a " & _
"ON trpm.IDTipeKemasan = a.IDTipeKemasan " & _
"ORDER BY trpm.ShiftKerja, ml.Location"
.Open

Could anyone help point out what I have been doing wrong here ?
Thank you very much for any suggestion given.

Regards,

Billy.

 

[Katy44]

Can you use VB to output the SQL statement that it's running? And then run that directly on the access database? That would make it a lot easier to see what's going wrong.

 

[hmckillop]

Nothing wrong with the statement except that the string has run out of space on one line. If you add in ' vbcrlf it should work e.g.

.Source = "SELECT trpm.IDTipeKemasan, trpm.ShiftKerja, ml.Location, " & _
"nm.NamaMesin, tm.TipeMesin, jp.JenisProduk, " & _
"tk.TipeKemasan, dp.NamaProduk, ds.Nama " & _
"FROM (((((((TempRekapProdMesin trpm " & _
"INNER JOIN TipeKemasan tk ON trpm.IDTipeKemasan = tk.IDTipeKemasan) " & _
"INNER JOIN MachineLocation ml ON trpm.LokasiMesin = ml.IDLoc) " & _
"INNER JOIN DataSupervisor ds ON trpm.IDSupervisor = ds.IDSupervisor) " & _
"INNER JOIN DataProduk dp ON trpm.IDProduk = dp.IDProduk) " & _
"INNER JOIN NamaMesin nm ON trpm.IDNamaMesin = nm.IDNamaMesin) " & _
"INNER JOIN TipeMesin tm ON trpm.IDTipeMesin = tm.IDTipeMesin) " & _
"INNER JOIN JenisProduk jp ON trpm.IDJenisProduk = jp.IDJenisProduk) " & vbcrlf & _
"JOIN " & _
"(SELECT trpm2.IDTipeKemasan, SUM(trpm2.JumlahMesin) AS tJumlahMesin, SUM(trpm2.JamRencana) AS tJamRencana, " & _
"SUM(trpm2.JamRealisasi) AS tJamRealisasi, SUM(trpm2.TolakUkur) AS tTolakUkur, SUM(trpm2.HasilProduksi) AS tHasilProduksi, " & _
"SUM(trpm2.DownTimeProduksi) AS tDownTimeProduksi, SUM(trpm2.DownTimeMesin) AS tDownTimeMesin, SUM(trpm2.LostTime) AS tLostTime, " & _
"SUM(trpm2.JumlahPekerja) AS tJumlahPekerja, " & _
"SUM(trpm2.Pengemasan) AS tPengemasan " & _
"FROM TempRekapProdMesin trpm2 " & _
"WHERE trpm2.IDPemakai = '" & sIDPemakai & "' " & _
"GROUP BY trpm2.IDTipeKemasan) a " & _
"ON trpm.IDTipeKemasan = a.IDTipeKemasan " & _
"ORDER BY trpm.ShiftKerja, ml.Location"
.Open

"I'm living so far beyond my income that we may almost be said to be living apart

 

[billyhs]

Thank you both for your help.
I have got it working now.

 

[7jennyh]

can you have two fields joined to the same field?

my example is two categories, both stored as code, but both referring to the same value list. can I produce a report that has both values?

I've tried to use inner joins, enforced both
but it returns nothing at all...

 

[Katy44]

In general, you would need to bring two (aliased) references of your value list into the report (or view, query, whatever). Each category should be joined to a different copy of the list.

Did this work?

If you need more help - what kind of report are you trying to produce.