Concatenating Memo Fields

[Matthew DeAngelis]

I have been working on this problem for several days now, using the
reference materials I have, and I have not been able to come up with a
solution. It is probably something very easy that I have missed.

I have some code that will, for each record, check several fields and,
if they are not null, insert the field name and its value (ie
'Technology: ' & [Technology] plus two carriage returns) into the field
I am using to concatenate them all. This is what I have:

Public Sub MergeMemoFields()
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "[tbl: Data - Deals]", cnn, adOpenDynamic, adLockOptimistic

Dim strCMC As String
rst.MoveFirst

Do Until rst.EOF = True
strCMC = ""
If Not IsNull(rst!Technology) Then
strCMC = strCMC & "'Technology: ' & rst!Technology & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & """
End If

If Not IsNull(rst!IP) Then
strCMC = strCMC & "'IP: ' & rst!IP &
Chr(13)+Chr(10)+Chr(13)+Chr(10) & "
End If

If Not IsNull(rst!BusinessModel) Then
strCMC = strCMC & "'Business Model: ' & rst!BusinessModel &
Chr(13)+Chr(10)+Chr(13)+Chr(10) & "
End If

If Not IsNull(rst!Issues) Then
strCMC = strCMC & "'Issues: ' & rst!Issues &
Chr(13)+Chr(10)+Chr(13)+Chr(10) & "
End If

If Not IsNull(rst!MarketOpportunity) Then
strCMC = strCMC & "'Market Opportunity: ' & rst!MarketOpportunity &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

Debug.Print strCMC
rst!ConcatMemoField = strCMC
rst.MoveNext
Loop

End Sub

It mostly does what it is supposed to do: if there is information in a
field, it inserts the string I have built. The problem is that it is
literally inserting the string - 'Technology: ' & rst!Technology &
Chr(13) & Chr(10) & Chr(13) & Chr(10) - instead of evaluating it and
placing the data in the field.

If anyone can help me out with this, I would be much obliged.


Matt

 

[Marshall Barton]

I have been working on this problem for several days now, using the
reference materials I have, and I have not been able to come up with a
solution. It is probably something very easy that I have missed.

I have some code that will, for each record, check several fields and,
if they are not null, insert the field name and its value (ie
'Technology: ' & [Technology] plus two carriage returns) into the field
I am using to concatenate them all. This is what I have: [snip]
strCMC = strCMC & "'Technology: ' & rst!Technology & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & """ [snip]
It mostly does what it is supposed to do: if there is information in a
field, it inserts the string I have built. The problem is that it is
literally inserting the string - 'Technology: ' & rst!Technology &
Chr(13) & Chr(10) & Chr(13) & Chr(10) - instead of evaluating it and
placing the data in the field.

You've got to many quotes, try this:

strCMC = strCMC & "Technology: " & rst!Technology _
& Chr(13) & Chr(10) & Chr(13) & Chr(10)

Note that in VBA you can use the built-in constant vbCrLf
(or vbNewLine) intead of the two Chrs. Not important, but
it might make your code a little more readable.

 

[Matthew DeAngelis]

I have been working on this problem for several days now, using the
reference materials I have, and I have not been able to come up
with a solution. It is probably something very easy that I have
missed.

I have some code that will, for each record, check several fields
and, if they are not null, insert the field name and its value (ie
'Technology: ' & [Technology] plus two carriage returns) into the
field I am using to concatenate them all. This is what I have: [snip]
strCMC = strCMC & "'Technology: ' & rst!Technology & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & """ [snip]
It mostly does what it is supposed to do: if there is information
in a field, it inserts the string I have built. The problem is
that it is literally inserting the string - 'Technology: ' &
rst!Technology & Chr(13) & Chr(10) & Chr(13) & Chr(10) - instead of
evaluating it and placing the data in the field.

You've got to many quotes, try this:

strCMC = strCMC & "Technology: " & rst!Technology _
& Chr(13) & Chr(10) & Chr(13) & Chr(10)

Note that in VBA you can use the built-in constant vbCrLf
(or vbNewLine) intead of the two Chrs. Not important, but
it might make your code a little more readable.

That works exactly as intended, both for the quotations and the newline
constants. Thank you very much


Matt