Text File Export Issue

[Jay]

This is a strange one that I can't seem to find a fix for.
We have a Billing DB application (Access 2000 format) where we upload
billing info in a comma delimited text file to our printer who prints and
mails our bills. The application basically creates a table then exports a
comma delimited file of that table.
The problem is that we keep getting one seemingly random record in the text
file that is misaligned. The record in question is truncated on one line
then repeated correctly on the next line. This of course throws off our
printers automation (printing presses seize up, paper flies and ink
splatters).

When looking at the original table, there is no hint of a problem. Also, the
table is a temp table and gets deleted and recreated each time we do this
process. The number of records is between 5000 to 12000 and again, only one
at random screws up in the exported text file. Any ideas?
Thanks,
Jay

[Salad]

Jay wrote:

This is a strange one that I can't seem to find a fix for.
We have a Billing DB application (Access 2000 format) where we upload
billing info in a comma delimited text file to our printer who prints and
mails our bills. The application basically creates a table then exports a
comma delimited file of that table.
The problem is that we keep getting one seemingly random record in the text
file that is misaligned. The record in question is truncated on one line
then repeated correctly on the next line. This of course throws off our
printers automation (printing presses seize up, paper flies and ink
splatters).

When looking at the original table, there is no hint of a problem. Also, the
table is a temp table and gets deleted and recreated each time we do this
process. The number of records is between 5000 to 12000 and again, only one
at random screws up in the exported text file. Any ideas?
Thanks,
Jay

If you were to run this process for the same recordset would the same
record be messed up? IOW, it may change each month, but if you were to
run it once, find the bad line, then reran the process would the same
record be incorrect?

ALso, since it appears this happens each month, does it go flakey on the
same field? IOW, let's say if flaked out on the address field, did it
flake out on the address field last month?

If you can't get a solution, the best suggestion would be to scrap your
export process if using Docmd.TransferT ext and write a routine to do it.
See Open, Close, Write, Print, Put in help. At least if a problem
occurs with a routine you write you can find the problem easily enough.

[Jay]

If I run the process again and/or re-export the same table I get the same
problem with the same record. If I run it again in another month, I get the
same problem but with a different record.

May 04 Output
"616-0601","4251-e04","*4251-e04*","NEW ENGLAND GOLF ASSOC.","300 ARNOLD
PALMER BLVD.",,"NORTON
NORTON","MA","0 2766-1365","American Publishing Service","PO Box
4560","Edwards" ,"CO","81632"," 800-330-8966","Golf
World","R12b2", $79.94,$39.97,2 ,92,6/11/2004 0:00:00,"71%"

Sept 04 Output
"1211-R03","19148-i04","*19148-i04*","LINDA MUIRCOMMANDER N
LINDA MUIR","60 DEER LA",,"GUILFORD" ,"CT","06437216 9","National Magazine
Service","PO Box
7570","Avon","C O","81620"," 800-330-8966","Flying", "R9b2",$52.00,$ 26.00,2,24,
10/2/2004 0:00:00,"46%"

So I see two different problems. The May 04 output truncated after the city
field and the Sept 04 output truncated after the name field then repeats the
name (albiet incomplete) on the next line.
What is interesting is that I can retype the info over the existing record
and I get the same error. I'll look into a routine as we are using the
transfet text command.
Jay
"Salad" <oi*@vinegar.co m> wrote in message
news:lE******** *********@newsr ead3.news.pas.e arthlink.net...

Jay wrote:

This is a strange one that I can't seem to find a fix for.
We have a Billing DB application (Access 2000 format) where we upload
billing info in a comma delimited text file to our printer who prints and mails our bills. The application basically creates a table then exports a comma delimited file of that table.
The problem is that we keep getting one seemingly random record in the text file that is misaligned. The record in question is truncated on one line
then repeated correctly on the next line. This of course throws off our
printers automation (printing presses seize up, paper flies and ink
splatters).

When looking at the original table, there is no hint of a problem. Also, the table is a temp table and gets deleted and recreated each time we do this process. The number of records is between 5000 to 12000 and again, only one at random screws up in the exported text file. Any ideas?
Thanks,
Jay

If you were to run this process for the same recordset would the same
record be messed up? IOW, it may change each month, but if you were to
run it once, find the bad line, then reran the process would the same
record be incorrect?

ALso, since it appears this happens each month, does it go flakey on the
same field? IOW, let's say if flaked out on the address field, did it
flake out on the address field last month?

If you can't get a solution, the best suggestion would be to scrap your
export process if using Docmd.TransferT ext and write a routine to do it.
See Open, Close, Write, Print, Put in help. At least if a problem
occurs with a routine you write you can find the problem easily enough.

[Salad]

Jay wrote:

If I run the process again and/or re-export the same table I get the same
problem with the same record. If I run it again in another month, I get the
same problem but with a different record.

May 04 Output
"616-0601","4251-e04","*4251-e04*","NEW ENGLAND GOLF ASSOC.","300 ARNOLD
PALMER BLVD.",,"NORTON
NORTON","MA","0 2766-1365","American Publishing Service","PO Box
4560","Edwards" ,"CO","81632"," 800-330-8966","Golf
World","R12b2", $79.94,$39.97,2 ,92,6/11/2004 0:00:00,"71%"

Sept 04 Output
"1211-R03","19148-i04","*19148-i04*","LINDA MUIRCOMMANDER N
LINDA MUIR","60 DEER LA",,"GUILFORD" ,"CT","06437216 9","National Magazine
Service","PO Box
7570","Avon","C O","81620"," 800-330-8966","Flying", "R9b2",$52.00,$ 26.00,2,24,
10/2/2004 0:00:00,"46%"

So I see two different problems. The May 04 output truncated after the city
field and the Sept 04 output truncated after the name field then repeats the
name (albiet incomplete) on the next line.
What is interesting is that I can retype the info over the existing record
and I get the same error. I'll look into a routine as we are using the
transfet text command.
Jay

There is one other thing you could check for. Go to a record that has a
problem. On the OnCurrent event, call a proc like the following.
CheckChars Me.Address

Private Sub CheckChars(var As Variant)
If Not IsNull(var)
Dim intI As Integer
Dim blnBad As Boolean
For intI = 1 to Len(var)
If ASC(Mid(var,int I,1)) < 32 or ASC(Mid(var,int I,1)) > 126 then
blnBad = True
Exit Form
Next intI
If blnBad then Msgbox "You have a character not alphanumeric"
Else
msgbox "The value is null."
Endif
ENd Sub

What this routine does it it reads every characters in the string the
field you passed. It's possible that a character that is not
alphanumeric is contained inside the field and does not display. IOW,
some data that "looks" correct in a field may contain a character that
Access does not know how to handle in the export process.

If you know how to program, you could open a recordset on the records
that were bad in May and Sept and pass the value to the above sub and
see if there is something that is outside of the scope of Ascii alphas.

[Jay]

Good thinking, I'll give this a shot.
Jay
"Salad" <oi*@vinegar.co m> wrote in message
news:RP******** ******@newsread 3.news.pas.eart hlink.net...

Jay wrote:

If I run the process again and/or re-export the same table I get the same problem with the same record. If I run it again in another month, I get the same problem but with a different record.

May 04 Output
"616-0601","4251-e04","*4251-e04*","NEW ENGLAND GOLF ASSOC.","300 ARNOLD
PALMER BLVD.",,"NORTON
NORTON","MA","0 2766-1365","American Publishing Service","PO Box
4560","Edwards" ,"CO","81632"," 800-330-8966","Golf
World","R12b2", $79.94,$39.97,2 ,92,6/11/2004 0:00:00,"71%"

Sept 04 Output
"1211-R03","19148-i04","*19148-i04*","LINDA MUIRCOMMANDER N
LINDA MUIR","60 DEER LA",,"GUILFORD" ,"CT","06437216 9","National Magazine
Service","PO Box
7570","Avon","C O","81620"," 800-330-8966","Flying", "R9b2",$52.00,$ 26.00,2,24, 10/2/2004 0:00:00,"46%"

So I see two different problems. The May 04 output truncated after the city field and the Sept 04 output truncated after the name field then repeats the name (albiet incomplete) on the next line.
What is interesting is that I can retype the info over the existing record and I get the same error. I'll look into a routine as we are using the
transfet text command.
Jay

There is one other thing you could check for. Go to a record that has a
problem. On the OnCurrent event, call a proc like the following.
CheckChars Me.Address

Private Sub CheckChars(var As Variant)
If Not IsNull(var)
Dim intI As Integer
Dim blnBad As Boolean
For intI = 1 to Len(var)
If ASC(Mid(var,int I,1)) < 32 or ASC(Mid(var,int I,1)) > 126 then
blnBad = True
Exit Form
Next intI
If blnBad then Msgbox "You have a character not alphanumeric"
Else
msgbox "The value is null."
Endif
ENd Sub

What this routine does it it reads every characters in the string the
field you passed. It's possible that a character that is not
alphanumeric is contained inside the field and does not display. IOW,
some data that "looks" correct in a field may contain a character that
Access does not know how to handle in the export process.

If you know how to program, you could open a recordset on the records
that were bad in May and Sept and pass the value to the above sub and
see if there is something that is outside of the scope of Ascii alphas.