andymartin3186
Member
- Local time
- Today, 12:47
- Joined
- Dec 10, 2024
- Messages
- 47
Hi,
Can anyone help identify where the issue is here.
I've created a query to pull job data to create a CSV to export to Xero which will automate invoicing my jobs.
The query runs fine on its own and displays everything I need correctly. Some fields are "" as Xero requires them present as a header so I just set them to ""
However when I run the CSV VBA code it errors on 3828 "cannot reference a table with a multi valued field using an in clause"
SQL
SELECT "INV" & Format([JobID],"00000") AS InvoiceNumber , Customers.CustomerName AS ContactName, Job.OrderNo AS Reference, Job.DateShipped AS InvoiceDate, "" AS DueDate, Job.SalesNotes AS Description, "1" AS Quantity, Job.Labour AS UnitAmount, "200" AS AccountCode, "20% (VAT on Income)" AS TaxType
FROM Customers INNER JOIN Job ON Customers.CustomerID = Job.CustomerID
WHERE (((Job.JobID)=[Forms]![Job Form]![JobID]));
VBA
Private Sub XeroExport_Click()
Dim Filename As String
Dim Filepath As String
Filename = "INV" & Format(Me.JobID, "00000")
Filepath = "C:\Users\Andy\Desktop\" & Filename & ".csv"
DoCmd.TransferText acExportDelim, , "XeroCSVQuery", Filepath, True
End Sub
Can anyone help identify where the issue is here.
I've created a query to pull job data to create a CSV to export to Xero which will automate invoicing my jobs.
The query runs fine on its own and displays everything I need correctly. Some fields are "" as Xero requires them present as a header so I just set them to ""
However when I run the CSV VBA code it errors on 3828 "cannot reference a table with a multi valued field using an in clause"
SQL
SELECT "INV" & Format([JobID],"00000") AS InvoiceNumber , Customers.CustomerName AS ContactName, Job.OrderNo AS Reference, Job.DateShipped AS InvoiceDate, "" AS DueDate, Job.SalesNotes AS Description, "1" AS Quantity, Job.Labour AS UnitAmount, "200" AS AccountCode, "20% (VAT on Income)" AS TaxType
FROM Customers INNER JOIN Job ON Customers.CustomerID = Job.CustomerID
WHERE (((Job.JobID)=[Forms]![Job Form]![JobID]));
VBA
Private Sub XeroExport_Click()
Dim Filename As String
Dim Filepath As String
Filename = "INV" & Format(Me.JobID, "00000")
Filepath = "C:\Users\Andy\Desktop\" & Filename & ".csv"
DoCmd.TransferText acExportDelim, , "XeroCSVQuery", Filepath, True
End Sub