Problem with Memo field contents to String Variable

[Martin Heal]

I am having problems trying to assign the contents of a Memo table
field to a String variable in VB using the following code:

Private Function udfBuildSQL(SQLID As Integer) As Variant

Dim rs As Recordset
Dim db As Database
Dim rSQL As String
Dim strSQL As String
Dim fld As Field
Dim fldLen As Long
Dim fldOffset As Long
Dim varChunk As Variant

Const cFldLen = 100
Set db = CurrentDb
rSQL = "SELECT * FROM tblSQL WHERE SQLID = " & SQLID

Set rs = db.OpenRecordset(rSQL)

Set fld = rs.Fields("SQLString")

fldLen = fld.FieldSize
fldOffset = 0

Do While fldOffset <= fldLen

varChunk = fld.GetChunk(fldOffset, cFldLen)
strSQL = strSQL & varChunk
fldOffset = fldOffset + Len(varChunk)
fldLen = fldLen - Len(varChunk)
If Len(varChunk) = 0 Then
Exit Do
Else: End If

Loop

udfBuildSQL = strSQL
End Function

Although the GetChunk retrieves the data correctly, the strSQL
variable doesn't take more than the first 250 or so characters. At
first I thought I might be hitting a similar limit to the 255
character maximum for a text table field, but have since confirmed
that this limit does not apply to string variables.

I have tried other methods to populate the strSQL variable, but it
still is limited to the first 250 or so characters.

Any suggestions or solutions would be greatly appreciated.

Thanks

Martin Heal

[MacDermott]

I find
DLookup("SQLString","tblSQL ","SQLID=" & SQLID)
will return the full string, even for a memo field longer than 250
characters.

I'm using Access 2000 - what about you?
"Martin Heal" <mh***@bigfoot.com> wrote in message
news:80**************************@posting.google.c om...

I am having problems trying to assign the contents of a Memo table
field to a String variable in VB using the following code:

Private Function udfBuildSQL(SQLID As Integer) As Variant

Dim rs As Recordset
Dim db As Database
Dim rSQL As String
Dim strSQL As String
Dim fld As Field
Dim fldLen As Long
Dim fldOffset As Long
Dim varChunk As Variant

Const cFldLen = 100
Set db = CurrentDb
rSQL = "SELECT * FROM tblSQL WHERE SQLID = " & SQLID

Set rs = db.OpenRecordset(rSQL)

Set fld = rs.Fields("SQLString")

fldLen = fld.FieldSize
fldOffset = 0

Do While fldOffset <= fldLen

varChunk = fld.GetChunk(fldOffset, cFldLen)
strSQL = strSQL & varChunk
fldOffset = fldOffset + Len(varChunk)
fldLen = fldLen - Len(varChunk)
If Len(varChunk) = 0 Then
Exit Do
Else: End If

Loop

udfBuildSQL = strSQL
End Function

Although the GetChunk retrieves the data correctly, the strSQL
variable doesn't take more than the first 250 or so characters. At
first I thought I might be hitting a similar limit to the 255
character maximum for a text table field, but have since confirmed
that this limit does not apply to string variables.

I have tried other methods to populate the strSQL variable, but it
still is limited to the first 250 or so characters.

Any suggestions or solutions would be greatly appreciated.

Thanks

Martin Heal