Error 3828 when creating a CSV from a query (1 Viewer)

can you try manual export using VBA:
Code: 
'=============================================
' Export Table or Query to CSV using raw file I/O
'=============================================

Public Sub ExportToCSVRaw(ByVal SourceName As String, Optional ByVal OutputPath As String = "")
' SourceName:       the name of table/query to export
' OutputPath:       the path + filename to export to
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Integer
    Dim i As Integer
    Dim lineOut As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SourceName, dbOpenSnapshot)
    
    If rs.EOF Then
        MsgBox "No records found in " & SourceName, vbInformation
        rs.Close
        Exit Sub
    End If
    
    ' if no OutputPath is passed, we save it
    ' to MyDocument with same name as the SourceName (.csv)
    If Len(OutputPath) = 0 Then
        OutputPath = Environ$("Userprofile") & "\Documents\" & SourceName & ".csv"
    End If
    
    ' Open file for writing
    f = FreeFile
    Open OutputPath For Output As #f
    
    '--- Write header line ---
    For i = 0 To rs.Fields.Count - 1
        lineOut = lineOut & QuoteCSV(rs.Fields(i).Name)
        If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
    Next i
    Print #f, lineOut
    
    '--- Write data lines ---
    Do While Not rs.EOF
        lineOut = ""
        For i = 0 To rs.Fields.Count - 1
            lineOut = lineOut & QuoteCSV(Nz(rs.Fields(i).Value, ""))
            If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
        Next i
        Print #f, lineOut
        rs.MoveNext
    Loop
    
    '--- Clean up ---
    Close #f
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Export complete: " & OutputPath, vbInformation
End Sub

'=============================================
' Helper: Quote and escape text for CSV
'=============================================
Private Function QuoteCSV(ByVal txt As String) As String
    Dim s As String
    s = CStr(txt)
    s = Replace(s, """", """""")  ' Escape quotes
    QuoteCSV = """" & s & """"
End Function
 
Sorry for the delay been a busy morning
I exported the query to excel without issue.

I didn't even think about the attachment fields being multi value.

I will try recommended steps
 
andymartin3186 said:
Sorry for the delay been a busy morning
I exported the query to excel without issue.

I didn't even think about the attachment fields being multi value.

I will try recommended steps
Click to expand...
I did say that my export worked fine despite an attachment field?
 
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.

Sorry please could you explain this, I'm not an expert by a long stretch.
 
I was not aware any export spec was used, other than the standard Access one?
 
arnelgp said:
can you try manual export using VBA:
Code: 
'=============================================
' Export Table or Query to CSV using raw file I/O
'=============================================

Public Sub ExportToCSVRaw(ByVal SourceName As String, Optional ByVal OutputPath As String = "")
' SourceName:       the name of table/query to export
' OutputPath:       the path + filename to export to
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Integer
    Dim i As Integer
    Dim lineOut As String
  
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SourceName, dbOpenSnapshot)
  
    If rs.EOF Then
        MsgBox "No records found in " & SourceName, vbInformation
        rs.Close
        Exit Sub
    End If
  
    ' if no OutputPath is passed, we save it
    ' to MyDocument with same name as the SourceName (.csv)
    If Len(OutputPath) = 0 Then
        OutputPath = Environ$("Userprofile") & "\Documents\" & SourceName & ".csv"
    End If
  
    ' Open file for writing
    f = FreeFile
    Open OutputPath For Output As #f
  
    '--- Write header line ---
    For i = 0 To rs.Fields.Count - 1
        lineOut = lineOut & QuoteCSV(rs.Fields(i).Name)
        If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
    Next i
    Print #f, lineOut
  
    '--- Write data lines ---
    Do While Not rs.EOF
        lineOut = ""
        For i = 0 To rs.Fields.Count - 1
            lineOut = lineOut & QuoteCSV(Nz(rs.Fields(i).Value, ""))
            If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
        Next i
        Print #f, lineOut
        rs.MoveNext
    Loop
  
    '--- Clean up ---
    Close #f
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  
    MsgBox "Export complete: " & OutputPath, vbInformation
End Sub

'=============================================
' Helper: Quote and escape text for CSV
'=============================================
Private Function QuoteCSV(ByVal txt As String) As String
    Dim s As String
    s = CStr(txt)
    s = Replace(s, """", """""")  ' Escape quotes
    QuoteCSV = """" & s & """"
End Function
Click to expand...
I've called that sub with the queryname and filename and get

3061
Too few parameters expected 1

Debug is at this line

Set rs = db.OpenRecordset(SourceName, dbOpenSnapshot)

Sourcename = XeroCSVQuery
dbopensnapshot = 4
 
Last edited:
OK I got this to work.
I had to remove from the query the criteria that makes it only display the current job ID that the form is on.

It then output the CSV perfectly (albeit without a filename but I can sort that) and as the criteria was removed it contained every JobID

I will use this version going forward. Can anyone help why it errors when running the code with the query criteria in place

[Forms]![Job Form]![JobID]

All this does is use the current form JobID to filter to this, which works when opening the query
 
Sorry to be a nuisance,
I'm a bit new to this,
How do I adjust the query to concatenate the form control value?

Alternatively where do I call that function?
 
andymartin3186 said:
OK I got this to work.
I had to remove from the query the criteria that makes it only display the current job ID that the form is on.

It then output the CSV perfectly (albeit without a filename but I can sort that) and as the criteria was removed it contained every JobID

I will use this version going forward. Can anyone help why it errors when running the code with the query criteria in place

[Forms]![Job Form]![JobID]

All this does is use the current form JobID to filter to this, which works when opening the query
Click to expand...
See if this additional approach helps.
Generic Recordset
 
To concatenate you would use

Code: 
WHERE Job.JobID= & [Forms]![Job Form]![JobID]

so that the query resolved to
WHERE Job.JobID=26 as an example

The function, you would use in @arnelgp's recordset example

Code: 
Set rs = fDAOGenericRst(SourceName, dbOpenSnapshot)
 
Last edited:
Gasman said:
To concatenate you would use

Code: 
WHERE Job.JobID= & [Forms]![Job Form]![JobID]

so that the query resolved to
WHERE Job.JobID=26 as an example

The function, you would use in @arnelgp's recordset example

Code: 
Set rs = fDAOGenericRst(SourceName), dbOpenSnapshot)
Click to expand...

I tired the concatenate WHERE but it doesn't like it and won't save, (missing operator)
 
Show the SQL.
You need to amend the actual SQL string.
Form has to be open as well?

I have always used Tempvars
Code: 
SELECT BP.BPID, BP.DateTime, BP.Systolic, BP.Heart, BP.MonitorMake
FROM BP
WHERE (((BP.BPID)=[Tempvars]![TT]));
1760635551508.png
 
Gasman said:
Show the SQL.
You need to amend the actual SQL string.
Form has to be open as well?

I have always used Tempvars
Code: 
SELECT BP.BPID, BP.DateTime, BP.Systolic, BP.Heart, BP.MonitorMake
FROM BP
WHERE (((BP.BPID)=[Tempvars]![TT]));
View attachment 121880
Click to expand...


SELECT "INV" & Format([JobID],"00000") AS InvoiceNumber, Customers.CustomerName AS Contact, 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, Job.JobID
FROM Customers INNER JOIN Job ON Customers.CustomerID = Job.CustomerID
WHERE Job.JobID= & [Forms]![Job Form]![JobID]
 
andymartin3186 said:
SELECT "INV" & Format([JobID],"00000") AS InvoiceNumber, Customers.CustomerName AS Contact, 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, Job.JobID
FROM Customers INNER JOIN Job ON Customers.CustomerID = Job.CustomerID
WHERE Job.JobID= & [Forms]![Job Form]![JobID]
Click to expand...
Ive tried

TempVars("jobid") = JobID.Value

and changing the WHERE to

WHERE Job.JobID= [Tempvars]![jobid]

Back to original error
 
I have generally done this in vba.
Try the tempvar approach which i have used in design view, or go Arnel's route with that function which will evaluate the parameters.
 
I do not know what to say. :(
Can you upload enough to see the issue?
 
The tempvar is working, as after clicking the button to do the csv, I can close the job form, run the query and it knows the jobID without prompting.

Code: 
Private Sub XeroExport_Click()

    Dim Filename As String
    Dim Filepath As String
    Dim queryname As String
 
     queryname = "XeroCSVQuery"
 
    Filename = "INV" & Format(Me.JobID, "00000")
    Filepath = "C:\Users\Andy\Desktop\" & Filename & ".csv"
    
    'DoCmd.TransferText acExportDelim, , "XeroCSVQuery", Filepath, True
    
Call ExportToCSVRaw(queryname, Filepath)
    
End Sub

Code: 
Public Sub ExportToCSVRaw(ByVal SourceName As String, Optional ByVal OutputPath As String = "")
' SourceName:       the name of table/query to export
' OutputPath:       the path + filename to export to
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Integer
    Dim i As Integer
    Dim lineOut As String
    
    TempVars("jobid") = JobID.Value
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SourceName, dbOpenSnapshot)
    
    If rs.EOF Then
        MsgBox "No records found in " & SourceName, vbInformation
        rs.Close
        Exit Sub
    End If
    
    ' if no OutputPath is passed, we save it
    ' to MyDocument with same name as the SourceName (.csv)
    If Len(OutputPath) = 0 Then
        OutputPath = Environ$("Userprofile") & "\Documents\" & SourceName & ".csv"
    End If
    
    ' Open file for writing
    f = FreeFile
    Open OutputPath For Output As #f
    
    '--- Write header line ---
    For i = 0 To rs.Fields.Count - 1
        lineOut = lineOut & QuoteCSV(rs.Fields(i).Name)
        If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
    Next i
    Print #f, lineOut
    
    '--- Write data lines ---
    Do While Not rs.EOF
        lineOut = ""
        For i = 0 To rs.Fields.Count - 1
            lineOut = lineOut & QuoteCSV(Nz(rs.Fields(i).Value, ""))
            If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
        Next i
        Print #f, lineOut
        rs.MoveNext
    Loop
    
    '--- Clean up ---
    Close #f
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Export complete: " & OutputPath, vbInformation
End Sub

'=============================================
' Helper: Quote and escape text for CSV
'=============================================
Private Function QuoteCSV(ByVal txt As String) As String
    Dim s As String
    s = CStr(txt)
    s = Replace(s, """", """""")  ' Escape quotes
    QuoteCSV = """" & s & """"
End Function

Code: 
SELECT "INV" & Format([JobID],"00000") AS InvoiceNumber, Customers.CustomerName AS Contact, 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, Job.JobID
FROM Customers INNER JOIN Job ON Customers.CustomerID = Job.CustomerID
WHERE Job.JobID= [Tempvars]![jobid];
 
You must log in or register to reply here.

Similar threads

P
Create seperate PDF Files from a report Base on a query with more than 300 records
Replies
5
Views
1,246
Uncle Gizmo
Uncle Gizmo
A
Solved TransferText to CSV Issues
2
Replies
39
Views
5,348
AngelSpeaks
A
A
CSV File Created, Phone Number Displayed with Special Char and Letters
2
Replies
33
Views
5,452
Pat Hartman
P
A
Solved Date Format Changed When Exporting to CSV
Replies
4
Views
3,003
Pat Hartman
P
nhorton79
Class object within Dictionary member variable of Class
Replies
11
Views
1,576
MajP
MajP

Users who are viewing this thread

  • Total: 2 (members: 1, guests: 1)
    Back
    Top Bottom