Losing character when exporting

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I have an output utility for producting custom text
files. When I output fixed width files I lose the last
character. A table of 4 columns with widths of
12,10,10,10 produces a text of 41 characters, 12,10,10 and
9. This happens whether the last field is empty, partial
or full. If I add 1 to the last field it is okay, but the
last field is decided by the user so it is not set. I
could track the last field and add one, but I should not
have to. Similiar code works in other programs so I am
hoping that it is just my inexpierence with Access that is
the problem.


Do While Not rs.EOF
rsOutName.MoveFirst
For i = 1 To outnumber 'number of fields selected
flen = rsOutName("FieldLen")
fld = rsOutName("FieldName")
LineToOutput = LineToOutput & Left(rs(fld) & Space
(100), flen)
rsOutName.MoveNext
Next i
Print #1, LineToOutput
rs.MoveNext
rsOutName.MoveFirst
LineToOutput = ""
Loop
 
Rich said:
I have an output utility for producting custom text
files. When I output fixed width files I lose the last
character. A table of 4 columns with widths of
12,10,10,10 produces a text of 41 characters, 12,10,10 and
9. This happens whether the last field is empty, partial
or full. If I add 1 to the last field it is okay, but the
last field is decided by the user so it is not set. I
could track the last field and add one, but I should not
have to. Similiar code works in other programs so I am
hoping that it is just my inexpierence with Access that is
the problem.


Do While Not rs.EOF
rsOutName.MoveFirst
For i = 1 To outnumber 'number of fields selected
flen = rsOutName("FieldLen")
fld = rsOutName("FieldName")
LineToOutput = LineToOutput & Left(rs(fld) & Space
(100), flen)
rsOutName.MoveNext
Next i
Print #1, LineToOutput
rs.MoveNext
rsOutName.MoveFirst
LineToOutput = ""
Loop
Click to expand...

How did you open the output file?
 
Dirk
Sorry, I left that part out, yes it is open. I exported 9
files, some very small, some very large, all perfect
except that each file lost the last character of every
line. Well, I should not say lost, it never received it.
I just monitored the output as each line was being built
and for a file of two fields, length 8 and 100, each line
was a combination of 8 and 99. Number of fields does not
matter, all but the last are the correct length. What has
got me going is that the code that builds the first 9
fields is the same code that builds the last.

Rich
 
Rich said:
Dirk
Sorry, I left that part out, yes it is open. I exported 9
files, some very small, some very large, all perfect
except that each file lost the last character of every
line. Well, I should not say lost, it never received it.
I just monitored the output as each line was being built
and for a file of two fields, length 8 and 100, each line
was a combination of 8 and 99. Number of fields does not
matter, all but the last are the correct length. What has
got me going is that the code that builds the first 9
fields is the same code that builds the last.

Rich
Click to expand...

I didn't ask *whether* you opened the output file, I asked *how* you
opened it, because I wanted to know if there was anything about the
attributes you specified at that time that might be relevant. I don't
know if that could be, but it would be nice to see all the code, not
just a snippet. For example, without seeing the declaration of
LineToOutput, I can't tell whether that was defined as a fixed-length
string.

At what point in the execution did you check the value of LineToOutput?
 
Dirk

Private Sub cmdOutput_Click()
With Me
Dim fld As String
Dim LineToOutput As String
Dim FilePath As String
Dim cnt As Integer

cnt = 0
Dim varItm As Variant
For Each varItm In lsttblname.ItemsSelected
cnt = cnt + 1
Next varItm

Dim db As Database, rs As Recordset
Dim rsOutName As Recordset
Dim OutName As Integer
Dim strSql As String
Dim mstart As Integer
Dim mend As Integer
Dim tblcnt As Integer
Dim ctlList As Control, varItem As Variant
Set db = CurrentDb()
Set ctlList = lsttblname

For Each varItem In ctlList.ItemsSelected
strSql = "Select Fieldname,Fieldlen from
tblOutput where len(indexer) > 0"
strSql = strSql & " AND tblname = '" &
ctlList.ItemData(varItem) & "' order by tblname,indexer"
Set rsOutName = db.OpenRecordset(strSql)
rsOutName.MoveLast: outnumber =
rsOutName.RecordCount: rsOutName.MoveFirst

Set rs = db.OpenRecordset("SELECT * FROM " &
ctlList.ItemData(varItem))
rs.MoveFirst

FilePath = txtPath.Value & ctlList.ItemData
(varItem) & ".txt"
Open FilePath For Output As #1

LineToOutput = ""
Do While Not rs.EOF
rsOutName.MoveFirst
For i = 1 To outnumber
flen = rsOutName("FieldLen")
fld = rsOutName("FieldName")
LineToOutput = LineToOutput & Left(rs
(fld) & Space(100), flen)
rsOutName.MoveNext
Next i
Print #1, LineToOutput
rs.MoveNext
rsOutName.MoveFirst
LineToOutput = ""
Loop
Close #1
MsgBox "File output process completed"
Next varItem
rs.Close
rsOutName.Close
End With
MsgBox "File output process completed"
End Sub

Rich
 
Rich said:
Dirk

Private Sub cmdOutput_Click()
With Me
Dim fld As String
Dim LineToOutput As String
Dim FilePath As String
Dim cnt As Integer

cnt = 0
Dim varItm As Variant
For Each varItm In lsttblname.ItemsSelected
cnt = cnt + 1
Next varItm

Dim db As Database, rs As Recordset
Dim rsOutName As Recordset
Dim OutName As Integer
Dim strSql As String
Dim mstart As Integer
Dim mend As Integer
Dim tblcnt As Integer
Dim ctlList As Control, varItem As Variant
Set db = CurrentDb()
Set ctlList = lsttblname

For Each varItem In ctlList.ItemsSelected
strSql = "Select Fieldname,Fieldlen from
tblOutput where len(indexer) > 0"
strSql = strSql & " AND tblname = '" &
ctlList.ItemData(varItem) & "' order by tblname,indexer"
Set rsOutName = db.OpenRecordset(strSql)
rsOutName.MoveLast: outnumber =
rsOutName.RecordCount: rsOutName.MoveFirst

Set rs = db.OpenRecordset("SELECT * FROM " &
ctlList.ItemData(varItem))
rs.MoveFirst

FilePath = txtPath.Value & ctlList.ItemData
(varItem) & ".txt"
Open FilePath For Output As #1

LineToOutput = ""
Do While Not rs.EOF
rsOutName.MoveFirst
For i = 1 To outnumber
flen = rsOutName("FieldLen")
fld = rsOutName("FieldName")
LineToOutput = LineToOutput & Left(rs
(fld) & Space(100), flen)
rsOutName.MoveNext
Next i
Print #1, LineToOutput
rs.MoveNext
rsOutName.MoveFirst
LineToOutput = ""
Loop
Close #1
MsgBox "File output process completed"
Next varItem
rs.Close
rsOutName.Close
End With
MsgBox "File output process completed"
End Sub
Click to expand...

I have to say that nothing pops out at me, though I don't see any
declaration for flen. Unless that's declared at the module or global
level, the fact that it's missing here may imply that you don't have
Option Explicit set for the module, which in turn could mean that you
have misspelled a variable somewhere and don't know it.

Also, I don't see the point of this:
cnt = 0
Dim varItm As Variant
For Each varItm In lsttblname.ItemsSelected
cnt = cnt + 1
Next varItm
Click to expand...

You don't appear to be referring to cnt anywhere else, and if all you
need to know is how many items are selected, what's wrong with

cnt = lsttblname.ItemsSelected.Count

?

Also, you have everything wrapped in a "With Me" block, but I don't see
that you're using that implicit qualifier anywhere.

All that said, I don't see a problem that would give you the results you
describe. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem,
compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it
to the address derived by removing NO SPAM from the reply address of
this message.
 
Dirk Goldgar said:
I have to say that nothing pops out at me, though I don't see any
declaration for flen. Unless that's declared at the module or global
level, the fact that it's missing here may imply that you don't have
Option Explicit set for the module, which in turn could mean that you
have misspelled a variable somewhere and don't know it.

Also, I don't see the point of this:


You don't appear to be referring to cnt anywhere else, and if all you
need to know is how many items are selected, what's wrong with

cnt = lsttblname.ItemsSelected.Count

?

Also, you have everything wrapped in a "With Me" block, but I don't
see that you're using that implicit qualifier anywhere.

All that said, I don't see a problem that would give you the results
you describe. If you'd like to send me a cut-down copy of your
database, containing only the elements necessary to demonstrate the
problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO
SPAM from the reply address of this message.
Click to expand...

Rich -

Now that I've looked at your database, I see that you didn't post all
the relevant code. This code (not shown above) is at fault:
If cboType <> "Fixed" Then
LineToOutput = Left$(LineToOutput, Len(LineToOutput) - 1)
End If
Click to expand...

The problem you're reporting arises when you export to fixed width, but
the actual value of cboType in this case is *not* "Fixed"; it's "Fixed
Width". So you yourself are dropping the last character from the line.
 
Dirk

Ouch, my head hurts. It was originally "Fixed", forgot to
change it. I was going to say in my original email that
it is probably something silly like using a comma instead
of a period. I guess this mistake is a good match for
that scenario.

Thanks
Rich
 
Rich said:
Dirk

Ouch, my head hurts. It was originally "Fixed", forgot to
change it. I was going to say in my original email that
it is probably something silly like using a comma instead
of a period. I guess this mistake is a good match for
that scenario.
Click to expand...

Oh, by the way, I believe the way you have been wrapping your code in
With Me ... End With would only improve efficiency (if at all) if you
also used control references that implied an object qualifier. For
example, you have many case that are like this:

With Me
Control1 = foo
Control2 = bar
End With

If you want VB to use the cached "Me" object reference, you need to
write this:

With Me
!Control1 = foo
!Control2 = bar
End With

or usually you can write this:

With Me
.Control1 = foo
.Control2 = bar
End With

The way you had it originally, I don't think the "With Me" statement
would have any effect whatever on the code execution -- Access will go
ahead and resolve the control names in the same way it would if you
hadn't used the With statement at all.

I don't know if using the With statement in one of the alternative ways
I suggested would actually have any detectable impact on execution
speed, since the Me object reference is already predefined. That is, I
don't know if

With Me
!Control1 = foo
!Control2 = bar
End With

is any faster than

Me!Control1 = foo
Me!Control2 = bar

It may be, but I doubt if it makes much difference. It's really a
different case than one where the object reference path needs to be
resolved, like

With Forms!frmMyForm

or

With Me!sfMySubform.Form!MyTextbox

I suppose some enterprising soul might run some performance benchmarks,
just to make sure.
 
Back
Top