Export Queries to Txt Problem.

[ions]

Dear MS Access Expert

Edited Question

I am having a problem with the Built in Access Export Wizard. It will not allow you to export a Query that has a form Reference in the criteria section within the Query Grid.

You can try this. Try to export ( to a text file ) a query that has a form Reference in the criteria section. You will get the Too few parameters error.


Original Questions. I was trying to approach this problem from the VBA side originally. Maybe it will help?

I am executing this code in VBA.

DoCmd.TransferText acExportDelim, "ShawDrum Export Specification", "ShawDrum", myDir & "Shaw_Drum.txt", True

When the query ShawDrum doesn't have the criteria [Forms]![ShawDrumUpload]![Driver] in one of its fields, the export works fine. However, if that criteria is added to the query... I get an error message from the Export Wizard. Too Few Parameters. Expected 1.

How do I solve this error. Should I create a QueryDef and pass the parameters into the querydef and then feed TransferText with the Querydef?



Thanks.

 

[ajetrumpet]

When the query ShawDrum doesn't have the criteria [Forms]![ShawDrumUpload]![Driver] in one of its fields, the export works fine. However, if that criteria is added to the query... I get an error message from the Export Wizard. Too Few Parameters. Expected 1.

If you have the criteria in your SQL statement, is it being fulfilled? In other words, is the relevant form open? And does it have a value in the [Driver] control??

If it doesn't you would most likely get the error you are seeing.

 

[ions]

The Form is open. When I execute the Query it works. When I Try to import it I get the error. See associated pic to confirm all necessary components are open.

I was thinking of putting an SQL statement in .TransferText and hardcoding the criteria into the SQL Statement. However, I read the help file on .TransferText and it only accepts a query. Any other work arounds for this??? I need this urgently and there must be a way to do it.


This is the final result I am after:

DoCmd.TransferText acExportDelim, "ShawDrum Export Specification", "ShawDrum", myDir & strFileName, True

 

[GolferGuy]

There are two ways I know of to get this error fixed. One way is to use the QueryDefs collection, reference this query through the QueryDefs collection and update it's Parameter with the value that is in the control on your form. The other way, which I have had much better luck with is to build the SQL in your VBA code and then use a variable to hold that SQL statement to pass to the TransferText command. If you follow the link in my signature Access Design Tips, you will find a downloadable utility to transform a SQL statement into VBA code (SQL to VBA Utility). Here is a paragraph from the documentation about adding a variable into the SQL so the VBA will be built to automatically add the value from a variable into the SQL for you.

1. Variable definition
Generally, the SQL string is being generated in VBA code so that variable data can be inserted into the generated SQL string. This utility uses the syntax of [mfldXfldName] to define where you want the VBA code to insert the variable information identified by the fldName in your code, from the module into the SQL string. So in your SQL statement, you would put “[mfldNOrderNumber]” to inform the SQL2VBA utility to produce the appropriate VBA code for inserting the variable order number into the final SQL string. Here is an illustration of that.

SELECT OrderNumber, Quantity, Item, ItemShipDate
FROM tblOrderDetail
WHERE tblOrderDetail.OrderNumber=[mfldNOrderNumber];

The generated VBA code will look like this:
strSql = "SELECT OrderNumber, Quantity, Item, ItemShipDate
FROM tblOrderDetail
WHERE tblOrderDetail.OrderNumber=" & [OrderNumber] & ";"


And the SQL statement generated by this VBA, using 86554 as the OrderNumber, will look like this:
SELECT OrderNumber, Quantity, Item, ItemShipDate
FROM tblOrderDetail
WHERE tblOrderDetail.OrderNumber= 86554;


Within the variable definitions, here are the separate parts.
“[mfld ]” defines this as a variable. The first character following the initial escape sequence of "[mfld" defines the type of variable being defined.
“T” is used for a text field. The SQL2VBA utility will put single quotes (') on each side of the inserted variable data.
“N” is used for a numeric field. Here the SQL2VBA utility will just insert the variable data with no quotes, just as numeric data should be represented in a SQL string.
“D” is used for a date field. This will cause the date variable data to have the hash mark/pound sign (#) on each side of the inserted date.

For the complete documentation of this utility, download the Word file using the Download link on the web site.

 

[ions]

GolferGUy I understand exactly what you are saying. I had the same issue when trying to use queries in VBA but that is not the problem I am having.

I am having a problem with the Built in Access Export Wizard. It will not allow you to export a Query that has dynamic criteria linked to a form's content.

You can try this. Try to export ( to a text file ) a query that has a form Reference in the criteria section. You will get the Too few parameters error.

I don't think I was very clear in my first post I am giong to edit it.

Thank you.

 

[GolferGuy]

Sorry, I missed that you were using the wizard. When Access is going to build a recordset, like it does in VBA, which must be the same process the wizard uses, it does not allow you to have parameters to be filled in at the time the SQL is run. It might work if you define the parameter within the query, but I don't know, just guessing as a possibility to try.

 

[gemma-the-husky]

Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel97, qryname, exportfile, True)

this is transferspreadsheet - turns a query into a csv, but definitely never has any problem, whatever references are in the query

[although thinking about it, i tend to use functions as criteria, rather than form references]

qryname is the name of the query and exportfile is the name of the file to create

 

[ions]

Thanks for all your responses. I will try to define the paramter in the query and the try the .csv

 

[ions]

.csv worked. I think the client will be OK with that. Thanks guys.