arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 23:28
- Joined
- May 7, 2009
- Messages
- 20,530
With these changes I'm getting run-time error 3061 Too few parameters. Expected 1.here again with some modifications.
before running the code, Kill Excel from Task Manager(Ctrl-Shit-ESC), then find Excel.
Right-click on the Excel Process, the choose End Task.
If arrQueries(i) = "SalesOrder_Query" Or arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then
I really appreciate your time to create this code but this time I'm back with the error 3828. Your approach from post #17 is the closest it's been to working for me.this is very fast, total re-code.
i remove the module and instead, change the query string if there is parameter.
see the code on the button
The following is a simple example (untested) which replaces a parameter referencing a txtDateApponted parameter with the value of the parameter: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?
Dim strSQL_Current as String
Dim strSQL_Temp As String
Dim qdf As DAO.Querydef
' replace queries SQL statement , substituting a date literal for the parameter
Set qdf = CurrentDb.querydefs("QueryNameGoesHere")
strSQL_Current = qdf.SQL
strSQL_Temp = Replace(trSQL_Current,"Forms!frmEmployees!txtDateAppointed", "#" & Format(Me.txtAppointed,"yyyy-mm-dd") & "#")
qdf.SQL = strSQL_Temp
' export query's result table to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryNameGoesHere", strFilePath, True
' reinstate query's original SQL property
qdf.SQL = strSQL_Current
maybe you can show us the SQL String of those two problematic queries.I really appreciate your time to create this code but this time I'm back with the error 3828. Your approach from post #17 is the closest it's been to working for me.
SalesOrder_Querymaybe you can show us the SQL String of those two problematic queries.
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)=[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]));
SELECT Shipments.Invoice_Number, Shipments.Invoice_Date, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Line_Value, [Line_Value]/100*20 AS LineTaxValue, Customer_Orders.Database_Entry_Created_By, Customer_Orders_Items.Product_Code, Products.Unit_of_Purchase, Customer_Orders_Items.Order_Quantity, Round(ACPbyDate([Customer_Orders_Items].[Product_Code],[Production_Complete_Date]),2)*[Customer_Orders_Items].[Order_Quantity] AS CostofSale, Sum(Shipment_CostofSale_Query.CostofSale) AS TotalCostofSale
FROM ((Shipment_CostofSale_Query INNER JOIN Shipments ON Shipment_CostofSale_Query.Order_Shipment_Number = Shipments.Order_Shipment_Number) INNER JOIN Order_Status ON Shipments.Shipment_Status = Order_Status.Order_Status_Code) INNER JOIN (Products INNER JOIN (Customers INNER JOIN (Customer_Orders INNER JOIN Customer_Orders_Items ON Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number) ON Customers.Customer_Ref = Customer_Orders.Customer_Ref) ON Products.Product_Code = Customer_Orders_Items.Product_Code) ON Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number
GROUP BY Shipments.Invoice_Number, Shipments.Invoice_Date, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Line_Value, [Line_Value]/100*20, Customer_Orders.Database_Entry_Created_By, Customer_Orders_Items.Product_Code, Products.Unit_of_Purchase, Customer_Orders_Items.Order_Quantity, Round(ACPbyDate([Customer_Orders_Items].[Product_Code],[Production_Complete_Date]),2)*[Customer_Orders_Items].[Order_Quantity]
HAVING (((Shipments.Invoice_Date)=[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]) AND ((Customer_Orders_Items.Order_Quantity)>0));
That's all correctyou don't get error when opening these 2 queries directly from the Navigation Pane. right? and only got error when Exporting them?
is the Export code from the same form, PowerBI_Export_Pop_Up_Form?
That did the trick! Thank you so much for your time to help me with the codecan you test this again, i made another module. and modify the code a bit.
Thanks for the explanation. Same approach as Ken suggested in his post. Still don't understand why I was getting error 3828 or why it didn't like the form reference in the query criteria.what the code does is Replace the form parameter criteria of the query with the actual value of Text2 on the form.
after running the TransferSpreadsheet, the query is re-instated to it's original.