Problem with using export wizard on a query (1 Viewer)

[hammerva]

I am trying to create an export specification for a transfer text command in my application. I am trying to take a query and export to an text file. Here is the query:

SELECT A.Release_Number, C.Status_Number, C.Status_Description, C.Current_Function_Points AS Function_Count, C.Project_Type, [Metrics Total Estimated Hours].Total_Estimated_Hours, [Metrics Total Estimated Hours].Total_Completed_Hours
FROM Releases AS A, Metrics AS C INNER JOIN [Metrics Total Estimated Hours] ON C.Status_Number = [Metrics Total Estimated Hours].Status_Number
WHERE (((A.Production_Date) Between [Forms]![Workbook Search]![Start_Date] And [Forms]![Workbook Search]![End Date]) AND ((C.SLC_Phase)='Analyze') AND ((A.Release_Number)=[c].[Release_Number]));


I run the query with no errors but when I try to go to the Export Text Wizard section I get an error saying 'Too few parameters. Expected 2'. I usually get this kind of message on invalid queries but if the query ran fine on its own why won't it work when doing an export?

Any ideas on what I am doing wrong here.

 

[Pat Hartman]

Is the workbook form open when you run the export wizard?

 

[hammerva]

I tried both ways and I got the same error message. I thought that maybe I needed to run the Metrics Total Estimated Hours query at the same time but that didn't do anything.

Actually I can't even get to the Export Wizard box. It fails when I click on Export in the Save/Export Dialog box.

It looks like it is an issue with using form fields in the WHERE clause. When I try to do an export with a query that doesn't use form fields it works fine. Is there any way around this in Access 97?

 

[Pat Hartman]

You can try using a function.

Public Function GetStartDate() As Date
GetStartDate = [Forms]![Workbook Search]![Start_Date]
End Function

If it still complains about the form field reference, you'll need to put the form field in a global variable before doing the export. Then the function will get the value from the global variable rather than the form field.

 

[hammerva]

That did the trick. I was able to create the Export specification without a problem.

You rule

 

[hammerva]

Actually I am still confused about one thing. I was able to create the text file with the export specification 'Functions Point Count'. But if I go to Excel with this text file, I still get the Text Import Wizard and I still have to set the field widths for the file. Why isn't the specification in the export not showing when I import the text file into Excel?

Thanks for the help

 

[Pat Hartman]

You should use the TransferSpreadsheet Method if your target is a spreadsheet. I don't think this will "fit" the columns either but, it is one less step. Once you create the spreadsheet, you can open it via OLE automation and "fit" the columns. I don't know the exact code to do this. To find out, I would create an excel macro that does what I want to do and go into the code window to copy the code to my Access app.

 

[P_Henry]

Hi Pat and Hammerva,

I have a similar problem and I am stuck ...not sure how to access the global variable with the date field from the query.
The way I have done this is after the date field gets altered, I am calling a function to set up the global variable from the form field.
The query is as shown below:

SELECT tblProgProjLink.ProjectNo
FROM tblProgProjLink
WHERE (((tblProgProjLink.Created)>=gLastRunDate)) OR (((tblProgProjLink.Modified)>=gLastRunDate))
GROUP BY tblProgProjLink.ProjectNo;

gLastRunDate is the global variable. When I run this query, the query asks for parameter value for the field though it is populated from the form.

Can't figure out what is wrong.

 

[Pat Hartman]

Queries are separate from Access. They are processed by the database manager, Jet and Jet has no knowledge of VBA variables. However, Jet will ask Access to resolve user defined functions. That means that you need to create a public function that returns your variable. Put the function in a standard module.

Public Function ReturnVar()
  ReturnVar = gLastRunDate
End Function

 

[P_Henry]

Dear Pat,
Thanks..

Sorry...I think I wasn't very clear.

When the user puts in a date on the form, I have an 'AfterUpdate' event coded. This calls the public function as you have indicated.

Option Compare Database
Option Explicit
Public gLastRunDate As Date


Public Function SetgLastRunDate() As Date

gLastRunDate = [Forms]![frmLNDownLoad]![cboLastDownLoadDate]

End Function

After the global variable is set, I want the query indicated above to make use of the global variable so that I can export and set up export specification. The query is not able to recognise the global variable.
Am I doing something incorrect?

 

[Pat Hartman]

I edited my previous response to change the typo "user defined queries" to "user defined functions".

You CANNOT refer to a VBA variable in a query but you CAN refer to a user defined function.

The query would then be:
...
Where somefield = ReturnVar();

This is the same solution I recommended for the original poster.

 

[P_Henry]

Thanks Pat...It worked well.
Cheers,
Priya