MS Access - STRING Datatype (truncating data)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am consolidating multiple cells (data) into one cell and when I do that, it
seems that when it finds large amount of data in one cell it does not select
the row and inserts into the second table. I am using VB code to move the
data.

Table1:

Column1 Column2
A 1
A 2
B 1
B 2
B 3
C 1

Table2:

Column1 Column2
A 1, 2
B 1, 2, 3
C 1

Except that instead of numbers they are characters (phases, sentenses). I
am not sure if the row is not selected or if the row is selected but not
inserted. I believe that this has to do with the number of characters in the
cell because for others rows with less characters in the cell the code works
perfect. The datatype in both tables is "MEMO". When selecting (VB code)
from the table, I declared the variable as a "STRING". Is the variable
(string) the problem? What should I change the variable to?
 
If you use DISTINCT in your Query / SQL then DISTINCT is the problem since
JET truncates Memo Field to 255 characters before comparing Field values as
required by DISTINCT.

HTH
Van T. Dinh
MVP (Access)
 
Please back up a little. There aren't cells in Access. What exactly are you
referring to? Is this a column in a query or a text box on a form/report?

You mention "VB code" but don't provide any details. Can you share your
code? If this is in a query, can you share your SQL view?
 
Sure! To answer your question it is a column. I do not think its the code
because it works for some data, and for other data it doesn't. The datatype
in the VB code for the strColumn4 is a Variant (also used String). Note, the
"Commentary" is a MEMO column and this column is assign to strColumn4. Here
is the VB code:

-----------------------------------------------------------------------------------------
Public Function FixTableComments() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strColumn1 As String, strColumn2 As String, strColumn3 As String,
strColumn4 As Variant, strColumn4a As Variant

Set db = CurrentDb()

sSQL = "SELECT Measure, Location, Period, Commentary FROM
tbl_Staging_Comments " _
& "WHERE Not IsNull(Commentary) Or Commentary <> ' ' ORDER BY
Measure, Location, Period, Commentary ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strColumn1 = rst!Measure
strColumn2 = rst!Location
strColumn3 = rst!Period
strColumn4 = rst!Commentary
strColumn4a = rst!Commentary

rst.MoveNext
Do Until rst.EOF
If strColumn2 = rst!Location And strColumn4a <> rst!Commentary Then
strColumn4 = strColumn4 & "; " & rst!Commentary
strColumn4a = rst!Commentary
ElseIf strColumn2 = rst!Location And strColumn4a = rst!Commentary Then
strColumn4 = strColumn4
strColumn4a = strColumn4
Else
sSQL = "INSERT INTO tbl_Staging_Comments_PBV (Measure, Location,
Period, Commentary) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "','" &
strColumn3 & "','" & strColumn4 & "')"
db.Execute sSQL
strColumn1 = rst!Measure
strColumn2 = rst!Location
strColumn3 = rst!Period
strColumn4 = rst!Commentary
strColumn4a = rst!Commentary
End If
rst.MoveNext
Loop

' Insert Last Record
sSQL = "INSERT INTO tbl_Staging_Comments_PBV (Measure, Location, Period,
Commentary) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "','" & strColumn3 &
"','" & strColumn4 & "')"
db.Execute sSQL
End If

Set rst = Nothing
Set db = Nothing

End Function
 
You might need to create another recordset based on tbl_Staging_Comments_PBV
and then use the AddNew method rather than the INSERT INTO sql.
 
I removed the "Commentary" from the Order By and it did not work. I read the
instruction for using AddNew method and there are some restrictions that I
cannot forfill (requires a unique index on the record in the underlying data
source). So this is also out of the questions. Any other options? Thanks
for all your help.
 
Are you suggesting that "tbl_Staging_Comments_PBV" is not an Access table or
doesn't have a primary key?
 
AddNew should work with an Access table that doesn't have a primary key. The
following code worked on a table with no primary key:

Public Sub TestAddNew()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT UserName, Total_Points FROM Players")
With rs
.AddNew
.Fields("UserName") = "Tom"
.Fields("Total_Points") = 33
.Update
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub
 
Duane, I found the problem. It is cause by apostrophe ('). Is there a fix
for this? Or is there a way to scan and remove this character from the
column? Thanks,
 
You can try the replace() function to replace a single apostrophe with two
apostrophes.
 
Thanks Duane. I added the replace() function in the query that loads the
data into tbl_Staging_Comments table, which is the table that this code is
initially selecting off of.

Thanks for all your help.
 
Back
Top