Error 3828 when exporting queries to Excel

ChrisMore

Member
Local time
Today, 21:22
Joined
Jan 28, 2020
Messages
220
Hello,

I am trying to export multiple queries onto one workbook (one query per worksheet) using the below code:

Code:
Dim strFilePath As String
    strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesOrder_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesInvoice_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Customers_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Products-Stock_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ProductGroup_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PurchaseOrder_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Suppliers_Query", strFilePath, True

"SalesOrder_Query", "SalesInvoice_Query" and "PurchaseOrder_Query" require a date which is entered on a form textbox [Text2]. The form also has a command button to run the above code. These queries reference the form as below:

Code:
[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]

The issue I'm having is when I run this code I am getting error 3828 Cannot reference a table with a multi-valued field using an IN clause that refers to another database. This is very puzzling because:
  1. My tables don't have any multi-valued fields.
  2. The error isn't there if I hard code the dates in the queries.
  3. If I remove a particular table from the query there is no error (but the table has no multi-valued fields)
Does anyone have any ideas what's going on?

Thanks in advance,
Chris
 
In your source queries try the following

Eval([Forms]![PowerBI_Export_Pop_Up_Form]![Text2])

and see if they suddenly work.
 
In your source queries try the following

Eval([Forms]![PowerBI_Export_Pop_Up_Form]![Text2])

and see if they suddenly work.
Thanks for the suggestion but I'm still getting the error :(
 
Hello,

I am trying to export multiple queries onto one workbook (one query per worksheet) using the below code:

Code:
Dim strFilePath As String
    strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesOrder_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesInvoice_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Customers_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Products-Stock_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ProductGroup_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PurchaseOrder_Query", strFilePath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Suppliers_Query", strFilePath, True

"SalesOrder_Query", "SalesInvoice_Query" and "PurchaseOrder_Query" require a date which is entered on a form textbox [Text2]. The form also has a command button to run the above code. These queries reference the form as below:

Code:
[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]

The issue I'm having is when I run this code I am getting error 3828 Cannot reference a table with a multi-valued field using an IN clause that refers to another database. This is very puzzling because:
  1. My tables don't have any multi-valued fields.
  2. The error isn't there if I hard code the dates in the queries.
  3. If I remove a particular table from the query there is no error (but the table has no multi-valued fields)
Does anyone have any ideas what's going on?

Thanks in advance,
Chris
How about showing the queries? (within code tags)
What happens if you run them one by one?
Which one is causing the error?
 
Rather than referencing the control as a parameter, an alternative approach would be to amend the querydef object's SQL property by concatenating the value of the parameter into the string expression, before calling the TransferSpreadsheet method. Be sure that the date literal is delimited with # characters, and is either in US date format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD.

The original SQL property can then be reinstated after calling the TransferSpreadsheet method.
 
How about showing the queries? (within code tags)
What happens if you run them one by one?
Which one is causing the error?
The two queries causing the error are "SalesOrder_Query" and "SalesInvoice_Query" because these both have the Customer_Orders table, which seems to be the issue (I removed the tables from the queries one by one until I no longer had the error). However, if I enter the date directly into the criteria for both queries, I do not get the error. So the error seems to be a combination of the Customer_Orders table and referencing the form control as the parameter.

SalesOrder_Query SQL is below:

Code:
SELECT Shipments.Order_Number, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Delivery_Date, Customer_Orders.Date_Order_Received, Customer_Orders.Order_Value, [Order_Value]/100*20 AS TaxValue, [Order_Value]+([Order_Value]/100*20) AS TotalGrossValue, Customer_Orders.Database_Entry_Created_By, Works_Order_Items.Manufactured_Product_Name, Works_Order_Items.Quantity, Shipments.Line_Value, [Line_Value]/100*20 AS LineTaxValue
FROM Customers INNER JOIN (((Customer_Orders INNER JOIN Shipments ON Customer_Orders.Order_Number = Shipments.Order_Number) INNER JOIN Works_Order_Items ON Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) INNER JOIN Order_Status ON Shipments.Shipment_Status = Order_Status.Order_Status_Code) ON Customers.Customer_Ref = Customer_Orders.Customer_Ref
GROUP BY Shipments.Order_Number, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Delivery_Date, Customer_Orders.Date_Order_Received, Customer_Orders.Order_Value, [Order_Value]/100*20, [Order_Value]+([Order_Value]/100*20), Customer_Orders.Database_Entry_Created_By, Works_Order_Items.Manufactured_Product_Name, Works_Order_Items.Quantity, Shipments.Line_Value, [Line_Value]/100*20
HAVING (((Customer_Orders.Date_Order_Received)=Eval([Forms]![PowerBI_Export_Pop_Up_Form]![Text2])));
 
Rather than referencing the control as a parameter, an alternative approach would be to amend the querydef object's SQL property by concatenating the value of the parameter into the string expression, before calling the TransferSpreadsheet method. Be sure that the date literal is delimited with # characters, and is either in US date format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD.

The original SQL property can then be reinstated after calling the TransferSpreadsheet method.
Thank you for the suggestion, however my lack of knowledge in the terminology you've used is holding me back in understanding what you mean. Are you able to simplify what you said or provide an example of what you mean?
 
Is text2 formatted as a date?
I have just tried a simple form and have no issues.

Code:
SELECT tblDaily.*, tblDaily.DailyDate
FROM tblDaily
WHERE (((tblDaily.DailyDate)=[Forms]![frmDateSearch]![txtDate]));

1757495231996.png


1757495203640.png
 
Yes, formatted as general date. I've never had an issue with this approach before. Plus it's working with my query "PurchaseOrder_Query" but this doesn't involve the Customer_Orders table. This is why I'm saying the issue is a combination of the Customer_Orders table and referencing the form control as the parameter.
 
I think you are going to have to upload a sample DB with enough to see this issue.
I believe what Ken was indicating was to modify the qdf to add the parameter as a hardcoded date by concatenating the control value. Just as you would if you were running the sql in vba.

I have to assume that Customer_Orders.Date_Order_Received is also a date?

You can use this to obfuscate the data. Just need enough to see the issue, but do test it first before uploading.
 
maybe you can use a Custom export routine like on the demo.
open Form1 and see code on the form and on module modExcelExport.
 

Attachments

maybe you can use a Custom export routine like on the demo.
open Form1 and see code on the form and on module modExcelExport.
Thanks for the attachment. I've just tested it on my database and the date entered in Text2 isn't having an impact on the exported records. All data is being exported from the query as if there is no date criteria.

[Forms]![PowerBI_Export_Pop_Up_Form]![Text2] is the criteria for the Date_Order_Received field.

It's working on the demo you attached so there's something I'm not changing for it to work with my setup.
 
Last edited:
I got this to work
Code:
    'strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"
    'save to Documents folder
    strFilePath = "F:\Temp\" & Format(Me.Text2, "ddmmyy") & ".xlsx"
    
    ' get the sql from the query definition
    sql = CurrentDb.QueryDefs(conQuery).sql
    
    ' provide the parameter to the query
    'sql = Mid$(sql, InStr(1, sql, "select "))
    sql1 = Replace$(sql, "[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]", "#" & Format$(Me.Text2, "mm/dd/yyyy") & "#")
    
    ' create recordset from query
    Set rs = CurrentDb.OpenRecordset(sql1)
    
    'export the recordset
    Call subExport(strFilePath, conQuery, rs)
    
    MsgBox "Done exporting!"
 
Got it working now, I think I removed [Forms]![PowerBI_Export_Pop_Up_Form]![Text2] from the query without realizing.

How do I amend the code so it includes the other queries in the same workbook? I also have
"SalesInvoice_Query", "Customers_Query", "Products-Stock_Query", "ProductGroup_Query", "PurchaseOrder_Query" and "Suppliers_Query".

But only "SalesInvoice_Query" and "PurchaseOrder_Query" require the date criteria.
 
for you queries with criteria (SalesInboice_Query and PurchaseOrder_Query) use manual export (the code i gave you).
for the rest, export normally with DoCmd.TransferSpreadsheet.
 
for the two problematic query, use manual export and the rest using TransferSpreadsheet.
something like this on a command button.
Code:
'/Code on form
Private Sub cmdExport_Click()

    Dim strFilePath
    Dim arrQueries As Variant
    Dim i As Integer
  
    If IsDate(Me.Text2) = False Then
        MsgBox "Please enter a valid date."
        Exit Sub
    End If
  
    ' construct the report list
    arrQueries = Array("SalesOrder_Query", "SalesInvoice_Query", "Customers_Query", "Products-Stock_Query", _
                    "ProductGroup_Query", "PurchaseOrder_Query", "Suppliers_Query")
                  
    'strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"
    'save to Documents folder
    strFilePath = Environ$("userprofile") & "\documents\" & Format(Me.Text2, "ddmmyy") & ".xlsx"
  
    For i = 0 To UBound(arrQueries)
        ' check if the Query name is SalesInvoice or PurchaseOrder
        If arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then
            ' manual export
            Call ExportManually(strFilePath, arrQueries(i))
          
        Else
            ' use TransferSpreadsheet
             DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, arrQueries(i), strFilePath, True
           
        End If
    Next
  
    MsgBox "Done exporting!"

End Sub

Private Sub ExportManually(Byval Path As String, ByVal QueryName As String)
  
    Dim rs As DAO.Recordset
    Dim sql As String
  
    ' get the sql from the query definition
    sql = CurrentDb.QueryDefs(QueryName).sql
  
    ' provide the parameter to the query
    sql = Mid$(sql, InStr(1, sql, "select "))
    sql = Replace$(sql, "[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]", "#" & Format$(Me.Text2, "mm/dd/yyyy") & "#")
  
    ' create recordset from query
    Set rs = CurrentDb.OpenRecordset(sql)
  
    'export the recordset
    Call subExport(Path, QueryName, rs)

    rs.Close: set rs = Nothing
End Sub
 
for the two problematic query, use manual export and the rest using TransferSpreadsheet.
something like this on a command button.
Code:
'/Code on form
Private Sub cmdExport_Click()

    Dim strFilePath
    Dim arrQueries As Variant
    Dim i As Integer
 
    If IsDate(Me.Text2) = False Then
        MsgBox "Please enter a valid date."
        Exit Sub
    End If
 
    ' construct the report list
    arrQueries = Array("SalesOrder_Query", "SalesInvoice_Query", "Customers_Query", "Products-Stock_Query", _
                    "ProductGroup_Query", "PurchaseOrder_Query", "Suppliers_Query")
                 
    'strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"
    'save to Documents folder
    strFilePath = Environ$("userprofile") & "\documents\" & Format(Me.Text2, "ddmmyy") & ".xlsx"
 
    For i = 0 To UBound(arrQueries)
        ' check if the Query name is SalesInvoice or PurchaseOrder
        If arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then
            ' manual export
            Call ExportManually(strFilePath, arrQueries(i))
         
        Else
            ' use TransferSpreadsheet
             DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, arrQueries(i), strFilePath, True
          
        End If
    Next
 
    MsgBox "Done exporting!"

End Sub

Private Sub ExportManually(Byval Path As String, ByVal QueryName As String)
 
    Dim rs As DAO.Recordset
    Dim sql As String
 
    ' get the sql from the query definition
    sql = CurrentDb.QueryDefs(QueryName).sql
 
    ' provide the parameter to the query
    sql = Mid$(sql, InStr(1, sql, "select "))
    sql = Replace$(sql, "[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]", "#" & Format$(Me.Text2, "mm/dd/yyyy") & "#")
 
    ' create recordset from query
    Set rs = CurrentDb.OpenRecordset(sql)
 
    'export the recordset
    Call subExport(Path, QueryName, rs)

    rs.Close: set rs = Nothing
End Sub
Do I also add SalesOrder_Query to this part of the code?
Code:
If arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then

If I don't I get the error 3828.

I then added the query like this:
Code:
If arrQueries(i) = "SalesOrder_Query" Or arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then
But now I'm getting run-time error 9 "subscript out of range"

1757503058320.png
 
Start learning to debug code, be it yours or others.
What is in tablename?
I would expect you would need to create a sheet if it does not exist?
 

Users who are viewing this thread

Back
Top Bottom