Transposing columns and rows

  • Thread starter Thread starter Lee Stafford via AccessMonster.com
  • Start date Start date
I have never taken VB as a class, so I am not that familiar with it, but I
did do what you suggested and found that afterupdate, the ptext variable is
correct. It adds the ' delimiter in this loop:
Do While InStr(i, pText, ",") > 0
i = InStr(i, pText, ",")
If Mid$(pText, i - 1, 1) <> "'" Then
pText = Left$(pText, i - 1) & "'" & Mid$(pText, i, 1) & Mid$(pText,
i + 1)
End If

Up to that point, it is correct. I do not understand the code here, so I
am not sure why it adds it.

boy, I can't wait until I take VB next semester.
 
That code is just looking for a comma and making sure there is a single
quote on its left and right. This should only occur if the field type is
text. This should not happen with either dates or numerics.
 
I,m not sure why, but it does. The variable is defined as a string. Does
that make a difference?
 
"That code is just looking for a comma and making sure there is a single
quote on its left and right. This should only occur if the field type is
text. This should not happen with either dates or numerics."

I'm not sure why it looks at the field value as "text". "But it does."

The variable "ptext" is defined as a string. Does this make a difference?
 
Try replace the DelimitIt function with this:

Private Function DelimitIt(pText As String) As String
On Error GoTo DelimitIt_Err
Dim i As Integer
i = 1
Do While InStr(i, pText, ",") > 0
i = InStr(i, pText, ",")
If Mid$(pText, i - 1, 1) <> Me.txtDelimiter Then
'pText = Left$(pText, i - 1) & "'" & Mid$(pText, i, 1) &
Mid$(pText, i + 1)
pText = Left$(pText, i - 1) & Me.txtDelimiter & Mid$(pText, i,
1) & Mid$(pText, i + 1)
End If
i = i + 2
Loop
i = 1
Do While InStr(i, pText, ",") > 0
i = InStr(i, pText, ",")
If Mid$(pText, i + 1, 1) <> Me.txtDelimiter Then
'pText = Left$(pText, i) & "'" & Mid$(pText, i + 1)
pText = Left$(pText, i) & Me.txtDelimiter & Mid$(pText, i + 1)
End If
i = i + 1
Loop
DelimitIt = pText

DelimitIt_Exit:
On Error Resume Next
Exit Function

DelimitIt_Err:
MsgBox Err & ": " & Error$, 16, "Error in DelimitIt"
Resume DelimitIt_Exit:

End Function
 
THAT FIXED IT!!!....Thanks. It works for both dates and numeric data as
well as text.

Thanks again,

Lee

This way of creating reports will be great!
 
Back
Top