My string variable cuts off at 255 characters

[Dave]

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?

 

[Lynn Trapp]

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

 

[Dave]

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.

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

'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?

 

[Marshall Barton]

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.

 

[Marshall Barton]

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?

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.

 

[Lynn Trapp]

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