Error 3828 when creating a CSV from a query

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
 
I get 3828 as?
? error(3828)
Application-defined or object-defined error

Do you have a MVF in any of those fields in the SQL?

Also why would a quantity be a text value?
 
Untitled.png


Quantity is purely assigned 1 as it is always 1, its 1 invoice line.
 
You have no IN clause either, so I am stumped. :(

Tell me about it!

The query runs fine (when given the job ID)

The VBA code was copied straight from another form which creates a csv from a query and works great. The only thing I changed was the query name and filename prefix.

Baffled!
 
I suspect there will be a table or query property responsible.
It would be helpful if you can upload the relevant objects from the database

If the fields Quantity and / or AccountCode are number fields, there should be no quotes in the query sql for those fields
 
Last edited:
I suspect there will be a table or query property responsible.
It would be helpful if you can upload the relevant objects from the database

If the fields Quantity and / or AccountCode are number fields, there should be no quotes in the query sql for those fields

The fields do not exist in the database, the query creates these just for the purpose of the csv. I couldn't think of another way to do this?
 
The fields do not exist in the database, the query creates these just for the purpose of the csv. I couldn't think of another way to do this?

I've also deleted all the fields which do not exist in the database and kept it to just invoice number, unit amount and contact and it still errors.
 
So the two fields I mentioned are in the CSV file and therefore text?
I can see issues with your table structure in terms of repeating fields etc but nothing that explains error 3828

Hopefully uploading a cut-down database will assist us to explain the problem.
 
If the query runs OK when activated directly, but messes up when run via TransferText, the question has to be "what is different?", right? The first big difference between a direct query and a TransferText is the Import/Export Specification. Can you manually create a new spec and test using that new spec? I'm trying to eliminate a certain type of corruption. Either it works or it doesn't - but if it works, you're back in business and if not, the problem is not likely to be corruption in the I/E spec.
 
If the query runs OK when activated directly, but messes up when run via TransferText, the question has to be "what is different?", right? The first big difference between a direct query and a TransferText is the Import/Export Specification. Can you manually create a new spec and test using that new spec? I'm trying to eliminate a certain type of corruption. Either it works or it doesn't - but if it works, you're back in business and if not, the problem is not likely to be corruption in the I/E spec.
Doc,
I read that post as a hardcoded job value and not a reference to the form control.:unsure:
@andymartin3186 can you clarify please?
 
The table Job has multiple Attachment columns.
Aren't those structurally also Multi Value Fields?
I thought you had something there, but ..........

I just added an attachment field to a table in my test db. Populated it on one of the records.
Then created a query not including that field (as did the O/P). Just a simple query, no join.
Query worked fine.
 
Just the query.
Let me try an export.

Edit:
Actioned from immediate window. Export worked fine.
1760604015222.png
 

Users who are viewing this thread

Back
Top Bottom