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

Matthew DeAngelis wrote:

>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.

--
Marsh
MVP [MS Access]

Marshall Barton wrote:

> Matthew DeAngelis wrote:
>
> > 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