How to concatenate string to memo

[Oliver Kinne]

Hello all,

I am desparately trying to concat a string field to a memo field and output the result into a text file or use it in a query. The problem is that, whatever I do, the result of the concatenation is cut off after 255 characters.

Example:
Table 1:
Title: string - 128 chars
Description: memo

Query 1:
TitleDescription: [Title] & " - " & [Description]

Output:
The famous dictionary of computer terms - This book covers a wide range of ......... and expl
Access for beginners - An easy introduction into the world of databases ....... with many illustr

The output always cuts off. If I try the same in a module and write some code using Print #1 or Put #1 or something like that, the result is still cut off. I tried to use GetChunk(offset, size) but that always returned the beginning of the memo field and not chunks from inside the memo field.

Here is my code:

public Function OutputToActinicCatalog()

DoCmd.Hourglass true

' Open query "Output to Actinic Catalog"
Dim DB as Database
Dim Records as Recordset

set DB = CurrentDb

' get contacts
set Records = DB.OpenRecordset("Output to Actinic Catalog")

' Create CSV file
Open CurrentProject.Path & "\Output to Actinic Catalog.txt" for Output Access Write as #1

Dim Output, I as Long
Dim Newline as string, Quote as string, Comma as string

Newline = Chr$(13) & Chr$(10)
Quote = """"
Comma = ","

Output = ""

While Not Records.EOF

' Write memo field to file
for I = 0 to Records.Fields("Description").FieldSize - 1 step 255
Output = Records!Description.GetChunk(I, 255) ' This ALWAYS returns the FIRST(!!!) 255 characters - why?
print #1, Output
next I

Records.MoveNext
Wend

Records.Close

Close #1

DoCmd.Hourglass false

End Function





Any help would be much appreciated.

Ta,
Oliver.

Hope this helps?
Oliver

[Chris Eastwood]

It looks like you are writing out the memo field in chunks of 255 characters (as in your For-Next' loop) - but the code there will miss out a 'chunk' (usually the last bit) if it's length is not a multiple of 255.


Here's some code I use all the time :


'
' Create the Named File
'
iFileNum = FreeFile
'
Open sFilename for binary Access Write as iFileNum
set oField = oRS.Fields("bitmap")
'
' get the length of the field and the number of chunks required
'
lFileLen = oField.FieldSize ' length of field
lChunks = lFileLen \ CHUNKSIZE ' whole number of chunks
lFragment = lFileLen Mod CHUNKSIZE ' last chunk size
'
' Write away the chunks to the file
'
for lCount = 1 to lChunks ' will not run if lFileLen < CHUNKSIZE
ReDim bChunk(CHUNKSIZE)
bChunk() = oField.GetChunk(((lCount - 1) * CHUNKSIZE), CHUNKSIZE)
Put iFileNum, , bChunk()
next
'
' Write the final (or first if lChunks = 0) chunk
'
ReDim bChunk(lFragment)
bChunk() = oField.GetChunk(lChunks * CHUNKSIZE, lFragment)
'
Put iFileNum, , bChunk()
Close iFileNum




- as you can see from the above sample, I can vary the 'CHUNKSIZE' value (it's just a constant in my program). It also cleans up the final 'chunk' that could be missing from your file when the last bit isn't quite '255' (the 'ChunkSize') characters long.


Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Oliver Kinne]

Thank you for your reply.

However, my problem is that GetChunk ALWAYS returns the FIRST(!) so many characters of the memo field.

For example:

memo field: This is a very long text field. There is heaps and heaps of text in here. Wow. Really big.
GetChunk(1,10) = "This is a "
GetChunk(10,10) = "This is a "
GetChunk(20,10) = "This is a "
...

I.e. GetChunk does NOT work in VB for Access. What am I doing wrong?


Hope this helps?
Oliver

[Chris Eastwood]

The code that I posted is used in an application that retrieves Bitmaps from an Access (not access2000) database - some of these can be huge (5 mb for TIFF files for instance), and I've not had a problem with the GetChunk method.

Try using the method I posted earlier - I know that the code looks pretty similar to the way your's works (or doesn't :)), but it could be a problem with the 'for-loop' where you are accessing the field length on each iteration of the loop (although doubtful, it's worth trying these things).


Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Oliver Kinne]

Thanks Chris. The problem was in the following line of my code:

Output = Records!Description.GetChunk(I, 255) ' This ALWAYS returns the FIRST(!!!) 255 characters - why?



If I use your method of creating a field object variable first and then using GetChunk() on that, then it works:

Dim MyField as Field

set MyField = Records.Fields("Description")

Output = MyField.GetChunk(I, 255) ' NOW IT WORKS!!! HURRAY!



Thank you very much for your help. Much appreciated. I can sleep in peace now. :-)

Oliver.

Hope this helps?
Oliver