Error 3828 when exporting queries to Excel (3 Viewers)

ChrisMore

Member
Local time
Today, 07:58
Joined
Jan 28, 2020
Messages
212
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.
 

Users who are viewing this thread

Back
Top Bottom