Creating fixed width text files in Access 2003

[Byzantine]

I am trying to create a fixed width text file in MS Access 2003 where the
records in the output file appear one after the other on the same line (6
records per line), rather than underneath each other on separate lines. The
file I'm trying to create is used by an old accounting system as a means of
importing data into its nominal ledger, and unfortunately it has to be in
this format.

I'm currently using the 'transfertext' function with an appropriate export
file specification. The file is created perfectly but it places the data
records underneath each other (on separate lines) rather than side by side on
the same line.

Is there anyway I can do this in Access?

Any help you can give me is much appreciated.

Many thanks.

 

[Sprinks]

Byzantine,

My approach would be to loop through the recordset, and build a fixed length
string for each six records, padding the string with spaces as necessary with
the Space function. After each six records, write the resulting string to a
new field, and reinitialize it.

Then you can query the resulting table for those records not having Null in
the new field.

Hope that helps.
Sprinks

 

[Albert D. Kallal]

Hum, that certainly is a differ format. (6 records on the same line???).

Anyway, since this format is so un-standard...then you will have to roll
your own....

You can actually write code to output text directly to a file.

So, I would roll my own. You need to checkout the open, and print commands
in the help.

Here is basic code shell that will output query to a txt file. Of course, I
will leave it up to you to polish the code, put it behind a button, and
popup up the file browse dialog etc. All that nice user interface stuff is
up to you..but the basic code shell to export looks like:

Dim strFile As String
Dim intF As Integer
Dim strLine As String
Dim rst As dao.Recordset
Dim strSql As String
Dim i As Integer

' set name of output file. (you
' could prompt user for the file name
' here)
strFile = "c:\my data\MyData.txt"

intF = FreeFile()
Open strFile For Output As #intF

strSql = "select * from tblCustomer where City = 'Edmonton'" & _
" and lastName Is not null order by LastName"

' note how I set conditions, and most importantly set the order
' of the data in the above. You can (and should) consider using
' a query for the above,a nd go
' strSql = "myqueryName"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While rst.EOF = False

For i = 1 To 6

strLine = Format(Nz(rst!LastName), "!@@@@@@@@@@") & _
Format(Nz(rst!FirstName), "!@@@@@@@@@@")

Print #intF, strLine;

rst.MoveNext
If rst.EOF = True Then
Exit For
End If
Next i
' done outputing 6 lines...send a new line char...
Print #intF, ""

Loop

Close intF

rst.Close
Set rst = Nothing

The above would output the FirstName, LastName (finxed lengh each of 10
chars) 6 to a line...

Also, if the field in the reocrd set is longer, then the format string..you
have to first truncate.
So, a better sytnax might be:


strBuf = left(nz(rst!LastName),10)

(the above is now a MAX of 10 chars..and will truncate the lenght of the
last name..yo then go

print #intF,format(strBuf,"!@@@@@@@@@@");

So, you don't have to put all the data into strLine in one swipe..but can
out each field one at a time...

Note the format commands, the @@@@ pad with spaces. If you elimaonte the !,
then padding is righ just

so:

? "<" & format("abc","!@@@@@@@@@@") & ">" gives
<abc >

and

? "<" & format("abc","@@@@@@@@@@") & ">" gives
< abc>

 

[Byzantine]

Albert

Many thanks for your help here. You've really helped me and I'm almost there:

However when I run the routine with my data I get the following error message:
Error 3061. Too few parameters. Expected 1.

This seems to be caused by the query referenced in my strSQL line - My
strSQL="GLTxnEVUPP" and it is a select query as you suggest but has selection
criteria that looks up data in a form, which is open (hidden) at the time the
routine is run. However if I remove this selection criteria the routine works
perfectly. The problem is that I really need this variable criteria.

Any ideas?

Also, once I've created my file I'd like to simply copy it to another
location as a backup file. Am I able to run a file copy command from within
Access?

Many thanks for your time and patience.

Regards

Byzantine

 

[Albert D. Kallal]

However when I run the routine with my data I get the following error
message:
Error 3061. Too few parameters. Expected 1.

This seems to be caused by the query referenced in my strSQL line - My
strSQL="GLTxnEVUPP" and it is a select query as you suggest but has
selection
criteria that looks up data in a form, which is open (hidden) at the time
the
routine is run. However if I remove this selection criteria the routine
works
perfectly. The problem is that I really need this variable criteria.

Open the that form..make it invisible...and then got the query tab...can you
open the query?

If you can't open the query..then it will not work in code. On the other
hand, I have always found using queries with conditions that references a
form a difficult ad messy approach. Perhaps you might just put the
conditions in the sql like my original example...

Anyway, get the query working...and you should be able to just open the
query. Try and get that working. Once you can open the query via the
standard interface..then you can go back to start working on getting the
code to run. No use working with the code until you can freely open that
query (and get no prompts) when you open it via the UI. So, get that form
open, make it invisible..and then try opening the query. (you can do/test
all of this via the UI..and not yet write any code). Once the query
works..then go back to working on the code..

Also, once I've created my file I'd like to simply copy it to another
location as a backup file. Am I able to run a file copy command from
within
Access?

You can use the FileCopy command
FileCopy "from","to"

 

[Byzantine]

Albert

Many thanks for getting back to me so quickly.

The query runs perfectly through the UI with the form open, without any
prompts for information from the user. I've also tried using the SQL syntax
instead of the name of the query as you suggest and I get the same error
message.

For reference the selection criteria I'm using in my query is:

=[Forms]![frm Board Selection]![BoardID]

If I replace this variable criteria with hard coded criteria such as "Like
A1" in the 'BoardID' field the routine works perfectly. It just doesn't like
variable criteria.

Regards

Byzantine

 

[Albert D. Kallal]

For reference the selection criteria I'm using in my query is:

=[Forms]![frm Board Selection]![BoardID]

If I replace this variable criteria with hard coded criteria such as "Like
A1" in the 'BoardID' field the routine works perfectly. It just doesn't
like
variable criteria.

In code, you would get:


strSql = "select * from tblCustomer where " & _
"BoardID = " & forms![frm Board Selection]!BoardID

debug.print strSql
msgbox strSql

Note how I put the debug.print and the msgbox command in the above...these
would would eventally be removed when you can see that the sql is correct...

 

[Byzantine]

Albert

Yes - there was an error in my SQL syntax, which was easier to see with your
debug msgbox script. Thanks for this - the routine now works perfectly.

Regards

Byzantine

For reference the selection criteria I'm using in my query is:

=[Forms]![frm Board Selection]![BoardID]

If I replace this variable criteria with hard coded criteria such as "Like
A1" in the 'BoardID' field the routine works perfectly. It just doesn't
like
variable criteria.

In code, you would get:


strSql = "select * from tblCustomer where " & _
"BoardID = " & forms![frm Board Selection]!BoardID

debug.print strSql
msgbox strSql

Note how I put the debug.print and the msgbox command in the above...these
would would eventally be removed when you can see that the sql is correct...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.attcanada.net/~kallal.msn