Join Key of "* Tbl" not in Record Set

[Garryg]

I have pulled all of the fields from two tables into a query and designed a form using that query and I get stuck on "Join key of "*.Tbl" not in Record Set. Can't seem to find anything in the help files. Can anyone suggest a good book on Access 2000?

 

[Zelandakh]

Paste the SQL into a reply.

 

[Garryg]

SELECT [caseid] & " - " & [casesub] AS CaseNumber, [Submissions Tbl].CaseID, [Submissions Tbl].CaseSub, [Submissions Tbl].SubmitDate, [Submissions Tbl].[Submitted by], [Submissions Tbl].SubmReport, [Owner Tbl].CustomerID, [Owner Tbl].CompanyName, [Submissions Tbl].OwnReport, [Owner Tbl].PhoneNumber, [Owner Tbl].FaxNumber, [Invoice to Tbl].CustomerID, [Invoice to Tbl].CompanyName, [Submissions Tbl].InvReport, [Invoice to Tbl].PhoneNumber, [Invoice to Tbl].FaxNumber, [Invoice to Tbl].BillingAddress, [Invoice to Tbl!City] & " " & [Invoice to Tbl!Stateorprovince] AS [City Prov], [Invoice to Tbl].PostalCode, [Submissions Tbl].Prelim, [Submissions Tbl].VetID, [Vet Tbl].VetName, [Submissions Tbl].Add, [Submissions Tbl].SpecimenID, [Specimen Tbl].Specimen, [Specimen Tbl].Class, [Specimen Tbl].Age, [Specimen Tbl].WDM, [Specimen Tbl].Sex, [Specimen Tbl].Dead, [Specimen Tbl].Alive, [Specimen Tbl].BarnFloor, [Specimen Tbl].Size, [Specimen Tbl].Mortality, [Specimen Tbl].Morbitity, [Specimen Tbl].Feed, [Specimen Tbl].Medication, [Specimen Tbl].Vaccinations, [Specimen Tbl].Signs, [Specimen Tbl].Other1, [Specimen Tbl].Serreq, [Specimen Tbl].OthReq<br>
FROM [Invoice to Tbl] INNER JOIN ([Specimen Tbl] INNER JOIN ([Vet Tbl] INNER JOIN ([Owner Tbl] INNER JOIN [Submissions Tbl] ON [Owner Tbl].CustomerID = [Submissions Tbl].Owner) ON [Vet Tbl].VetID = [Submissions Tbl].VetID) ON [Specimen Tbl].SpecimenID = [Submissions Tbl].SpecimenID) ON [Invoice to Tbl].CustomerID = [Submissions Tbl].[Invoiced to];

 

[Zelandakh]

SQL looks ok.<br>
<br>
The only thing I can think of is that the software is getting confused over the table names as there are spaces in the names.<br>
<br>
If you are not too far down the line with the database, rename in a similar fashion to the following:<br>
<br>
submissions tbl becomes tblsubmissions<br>
invoice to tbl becomes tblinvoiceto<br>
<br>
and so on - generally use tbl or qry or frm as the first 3 letters and follow it with a description with no spaces.<br>
<br>
Failing that, we could be looking at an early prize for wanting Office 2000 Professional Service Pack 1...