0

So I have a bunch of tables all linked together someway or another, and I'm trying to make a report that lists all the items in a selected range of invoice numbers, and to begin the process, the User will click a button, and that button will open a pop up form with 3 fields, FirstInvoiceNumber, LastInvoiceNumber, and Client. These fields than save what the user enters as globals which are then plugged into the SQL statement in the Report_Open Event. There was a syntax error, but after about half an hour I found and fixed it. but now, I'm getting this Run-time Error 2465, which says "Microsoft Access can't find the field 'l1' (In front of the 1, its a symbol I don't know how to type, but it looks like a tall lowercase L) referred to in your expression." Maybe I'm just looking at this for too long, but the only "1" I can see, is where the SQL statement has the Initials field, and both of them are inside parenthesis, so I'm not sure whats wrong. Anyways, here is this ridiculous SQL (Note: I find it easier to read on one line and just scroll sideways rather than having a big chunk split onto multiple lines, so this SQL statement is fit onto only 2 lines, I'll put an extra paragraph space so it's easier to find it)

Me.RecordSource = "SELECT Invoices.InvNo, 
Invoices.JobNo, 
Invoices.InvDate, 
Invoices.TimeTotal, 
Invoices.Tax, 
Invoices.POTotal, 
Invoices.SubTotal, 
Invoices.InvTotal, 
Invoices.DatePaid, 
Invoices.AmountPaid, 
Invoices.Terms, 
Contacts.EMail, 
Invoices.Status, 
' & 'Attn: ' & [FirstName] & ' ' & [LastName] & ' AS FullName, 
Companies.CompanyFullName, 
Companies.CompanyAbrv, 
Contacts.Department, 
' & [City] & ', 
' & [State] & ' ' & [Zipcode] & ' AS AddressLine3, 
Invoices.ClientPONo, 
InvLineItems.LineItemNote, 
InvLineItems.Adjustments, 
InvLineItems.Status, 
InvLineItems.Cost, 
[Cost]+([Cost]*[Adjustments]) AS AAC, 
' & [Contacts].[AddressLine1] & ', 
' & [Contacts].[AddressLine2] & ' AS AddressLine1and2, 
' & [Invoices].[InvNo] & '-' & [Invoices].[JobNo] & ' AS InvNoandJobNo, 
Left([Contacts].[FirstName], 
1) & Left([Contacts].[LastName], 
1) AS Initials, 
Jobs.JobTitle, 
& ' & [Jobs].[JobTitle] & ' ' & [Jobs].[ClientCode] & ' AS Product, 
InvLineItems.Requester, 
Companies.CompanyID' _
& 'FROM ((((Invoices 
LEFT JOIN (Contacts 
RIGHT JOIN Jobs ON Contacts.ContactID = Jobs.ContactID) ON Invoices.JobNo = Jobs.JobNo) 
LEFT JOIN InvLineItems ON Invoices.InvNo = InvLineItems.InvNo) 
LEFT JOIN PurchaseOrders ON Invoices.InvNo = PurchaseOrders.InvNo) 
LEFT JOIN POLineItems ON PurchaseOrders.PONo = POLineItems.PONo) 
LEFT JOIN Companies ON Jobs.CompanyID = Companies.CompanyID'
ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
Mike Lee
  • 1
  • 1
  • first time posting on this site and it took the second half of my code and put it in that gray box, not sure why it only got that half but i can repost if this is too strenuous on the eyes – Mike Lee May 08 '18 at 19:12
  • I could be wrong here, but it looks like this line is wrong somehow: `LEFT JOIN (Contacts RIGHT JOIN Jobs ON Contacts.ContactID = Jobs.ContactID) ON Invoices.JobNo = Jobs.JobNo)` – ccarpenter32 May 08 '18 at 19:22
  • Oh and the symbol you may be referring to is the 'pipe' "|" it's the shifted version of the '\'-key – ccarpenter32 May 08 '18 at 19:24
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- You seem to be missing a space before `FROM`. – Andre May 08 '18 at 22:23
  • Replace single quotes by double quotes and use SQL debug technique mentioned by @Andre – Sergey S. May 09 '18 at 03:44
  • I'm not sure if this helps, but this worked fine in the query builder, it wasn't until i put it in VBA that it started having problems, so all the joins and such are fine – Mike Lee May 09 '18 at 19:43
  • and it wasn't the missing space before FROM – Mike Lee May 09 '18 at 19:44

0 Answers0