Transposing columns and rows

[Lee Stafford via AccessMonster.com]

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.

 

[Duane Hookom]

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.

 

[Lee Stafford via AccessMonster.com]

I,m not sure why, but it does. The variable is defined as a string. Does
that make a difference?

 

[Duane Hookom]

I'm not sure what you mean by "but it does" or which "variable"?

 

[Lee Stafford via AccessMonster.com]

"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?

 

[Duane Hookom]

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

 

[Lee Stafford via AccessMonster.com]

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!