My string variable cuts off at 255 characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 31 Jan 2005 16:49 GMT

I try to assign a string variable the following value:

'Load subform recordset
Dim sSQL As String

sSQL = "EssayNote.NoteID, EssayNote.EssayID, " _
& "EssayNote.EssayNote, Note.NoteShort, " _
& "EssayNote.Suppress, EssayNote.CreateDate, " _
& "EssayNote.NoteRankID " _
& "FROM [Note] INNER JOIN EssayNote ON Note.NoteID=EssayNote.NoteID " _
& "WHERE EssayNote.EssayID=" & txtEssayID & " AND EssayNote.Suppress=No
" _
& "ORDER BY createdate DESC, noteid;"

Forms![F_ESSAY]![F_ESSAY_SUB].Form.RecordSource = sSQL
Forms![F_ESSAY]![F_ESSAY_SUB].Form.Requery

This code produces the following error:

ERROR: F_ESSAY.Form_Load
1/31/2005 8:37:48 AM
Err.Number: 3163
Err.Description: The field is too small to accept the amount of data you
attempted to add. Try inserting or pasting less data.

This is what I have in my sSQL variable when I check the locals window

"EssayNote.NoteID, EssayNote.EssayID, EssayNote.EssayNote, Note.NoteShort,
EssayNote.Suppress, EssayNote.CreateDate, EssayNote.NoteRankID FROM [Note]
INNER JOIN EssayNote ON Note.NoteID=EssayNote.NoteID WHERE
EssayNote.EssayID=41 AND EssayNote.Suppres"

Counting the quotes this is 255 characters.

What is going on? A string should not be limited to 255 characters?

Reply to this Message

Lynn Trapp - 31 Jan 2005 18:59 GMT

While a string variable can be very large (around 2 billion bytes), the
RecordSource property of a form is limited to 255 characters.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

> I try to assign a string variable the following value:
>
[quoted text clipped - 33 lines]
>
> What is going on? A string should not be limited to 255 characters?

Reply to this Message

Dave - 31 Jan 2005 19:32 GMT

Thanks

So in this case I must use a predefined query as my recordsource?

> While a string variable can be very large (around 2 billion bytes), the
> RecordSource property of a form is limited to 255 characters.
[quoted text clipped - 36 lines]
>>
>> What is going on? A string should not be limited to 255 characters?

Reply to this Message

Marshall Barton - 31 Jan 2005 20:21 GMT

>While a string variable can be very large (around 2 billion bytes), the
>RecordSource property of a form is limited to 255 characters.

If that were true, I'd have blown it long ago ;-)

According to Help (Access specifications), the number of
characters in a RecordSource SQL statement is 32,750.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Lynn Trapp - 31 Jan 2005 20:45 GMT

As usual, Marsh, you are dead on. I was reading the wrong spec. when I
posted that. My apologies to Dave.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

>>While a string variable can be very large (around 2 billion bytes), the
>>RecordSource property of a form is limited to 255 characters.
[quoted text clipped - 3 lines]
> According to Help (Access specifications), the number of
> characters in a RecordSource SQL statement is 32,750.

Reply to this Message

Marshall Barton - 31 Jan 2005 20:41 GMT

>I try to assign a string variable the following value:
>
[quoted text clipped - 31 lines]
>
>What is going on? A string should not be limited to 255 characters?

I can't explain how you got that message, because Access
does not refer to a variable as a field. I don't even see
where that code refers to a field at all. The error is
certainly not because a string variable or a property can
not contain that many characters.

Maybe there's some other code that caused the error?

Here's some other issues to considerL

The SQL statement is missing the SELECT keyword???

Because of linewrapping, I can't tell if the lines are
properly split. From here, it looks like the Where clause
is incorrectly continued on another line.

Also, it's a waste of time to Requery the form immediately
after setting the RecordSource.

Signature

Marsh
MVP [MS Access]

Reply to this Message