Text report with set number of spaces and positioning

[a2ari]

Hello all,
Here are my complications. I need to create a number of Reports that output to text files. So far so good using VB to specify the output type (except for that blank space every other line problem). Now, I need for each field to start at a specific position on each line. I also need to maintain blank spaces.
ie.:
First field is 20 characters (bytes) long, starting at position 1
Second field is 180 (bytes) characters long, starting at position 21

Problem #1: Is there an easier way than trial and error to get exactly the field length that I need? More to the point, to get each field to start at the correct position...

Problem #2: How do I force blank spaces? Right now, if my 180 byte field only has 40 characters in it, it carriage returns right there. How do I force the blank spaces till the end of the line?

Thank you!

 

[shudini]

If you export using the docmd.transfertext or transferText macro, or if you just right click on a table or query and export it to a text file, one of the options is using fixed width. Once you choose fixed width, you can create a specs file that specifies exactly how many spaces each field should be padded to. Once you save those specs, you can use them from macros and vba, and Access will handle the padding of all the spaces you specify.

 

[a2ari]

Thanks for the help, problem is, I'm not getting an Export Text Wizard like people talk about. I'm getting a basic 'save to' window, I change it to .txt format, name it something and save it. I'm not getting any formatting options... doing something wrong?

 

[shudini]

Just to get this clear, are you trying to export a table or query, or are you trying to 'export' a report? Also, please detail how you are trying to save it.

 

[a2ari]

I'm right clicking on a table, click on 'Export...', Save as type: .txt
I'm not getting any "Export wizard" other than the 'Export Table "whatever" as'. I'm expecting something that allows me to create a schema or format my export, from what I've been reading, but that's not happening.
I've also tried using the TransferText Method, but I don't think that I have the SpecificationName right, cause It's telling me that the spec doesn't exist.

 

[shudini]

In the 'Save as type' field, there are two .txt options, one called "Microsoft Word Merge", and one called "Text Files". Could it be that you are choosing the Microsoft Word Merge instead of the Text Files?

 

[a2ari]

Look at you with all your knowledge of things that I'm doing wrong...!

thank you, and thank you... oh, did I mention thank you? I've been bashin my head against the wall looking all over the web and help files trying to find the export wizard. I wouldn't have noticed that difference, or realized my mistake.

 

[shudini]

Always a pleasure. Glad you got that working.

 

[a2ari]

ok, new problem has arisen...
the export wizard works like a charm on 2 of my queries, but a third one is giving me a vague error:
"Too few parameters. Expected 1."
Know what that means?

There is a criteria in the query. It works when I'm just running the query, or creating a report off of it, but might it be what's causing the export error?

thanks.

 

[shudini]

can you please post the sql of the query?

edit** Also, what version of MS Access are you running?

 

[a2ari]

Access 2003
query:

SELECT tblDate.Date, tblFOO.Prefix, tblFOO.BU, tblFOO.OoE, tblFOO.Credit, tblFOO.Amount, tblFOO.Circuit
FROM tblFOO INNER JOIN tblDate ON tblFOO.FOOID = tblDate.ID
WHERE (((tblFOO.FOOID)=[forms].[frmFOO].[id]));

So, I've got a form with a date field and a sub-form with all the information that relates to that date.

 

[shudini]

Access won't let you export the data when the query is parameter based. In your case, it is looking for a parameter from the form.

Are you runnning this export query from a form with a button? If so, you can code it so that it actually replaces the query with the frmFoo.ID using the queryDefs object.

 

[a2ari]

Are you runnning this export query from a form with a button? If so, you can code it so that it actually replaces the query with the frmFoo.ID using the queryDefs object.

Not doing it through code yet, but I will be creating a button for it.
I thought that you couldn't use the Transfer Text Method on code... I thought that you have to point it to a table or query. Can you Transfer Text on SQL code?

 

[shudini]

you have to point it to a query or table, but you can use code to dynamically alter the sql of a query.

 

[a2ari]

I'm not all that great at the coding...
can you show me how/point me to where I can see that?

 

[shudini]

First create a query and save it, the SQL of the query is irrelevant. For our case we will call it MyQuery.
Then, create a button and the OnClick event should be:

Dim daoQueryDef As DAO.QueryDef
Dim strSQL as string
Set daoQueryDef = CurrentDB.QueryDefs("MyQuery")
strSQL="SELECT tblDate.Date, tblFOO.Prefix, tblFOO.BU, tblFOO.OoE," & _
"tblFOO.Credit, tblFOO.Amount, tblFOO.Circuit " & _
"FROM tblFOO INNER JOIN tblDate ON " & _
"tblFOO.FOOID = tblDate.ID WHERE " & _
"(((tblFOO.FOOID)=[B]#[/B]" & [forms].[frmFOO].[id] & "[B]#[/B]))"
daoQueryDef.SQL=strSQL
TransferText "MyQuery"....

Since it seems your frmFOO.ID is of date/time data type, I included the hash symbols, if this is not the case, then for number type leave them out, and for text replace them with single quotes.

 

[a2ari]

I missed this when you first posted it, didn't get the subscription auto email... just saw it. Thanks.

Getting an "Item not found in this collection." Error... whatever that means.

My problem still with Transfer Text is that I still can't create the schema for the report. I've been creating the schema by exporting and using the wizard to create the schema that I'm calling in the Transfer Text method... so, I still can't create that schema... It's still giving me the same error message "Too few parameters. Expected 1."
Also, I found something that showed me how to create a schema.ini programatically, but I don't know how to call that as opposed to the schemas that the wizard creates...

 

[shudini]

First of all, when you are in the VBA IDE, go to Tools>References, and make sure you have a reference to the Microsoft DAO Object Library. Second, make sure that you created and saved a query with the name MyQuery.

Next, when you are in the Export Text Wizard, you can click on Advanced... to access the specs screen. This screen allows you to create a specs file and use the Save As... button to save this specs file to your database. Once you save these specs with a name, you can use the docmd.transferText acExportFixed, "SPECS_NAME_HERE_WITH_QUOTES" to reference that specs file.

Let me know what happens.

 

[a2ari]

I did that with my other queries and it's working like a charm. The problem is that I can't even get into the export text wizard off of my initial (problematic) query. What I'm going to try is creating a dummy query with the fields that I need, then create the schema with that. Then I'll point that schema with the VB... That might be what you're saying

 

[shudini]

precisely.

The specs are actually stored in hidden access tables. If you want to see what they look like, you can create a query with the SQL

SELECT * FROM MSysIMEXSpecs
and
SELECT * FROM MSysIMEXColumns

(Just in case you were interested)