[AccessD] Error Message: The field is to small to accept the amount of data you tried to add. Try inserting or pasting less data

Hi jeffrey

If updating/appending a recordset in code you can use something like
this:

  Set fld = rstA!TextA
  fld.Value = Left(rstB!SomeField & strString, fld.Size)

For a query I believe you'll have to create an external function to
obtain the size of the receiving text field.
Here's a quick one-liner for this purpose:

<code>

Public Function FieldSize( _
  ByVal strTable As String, _
  ByVal strField As String) As Byte

  On Error Resume Next
  FieldSize = DBEngine(0)(0).TableDefs(strTable).Fields(strField).Size
  
End Function

</code>

Now, put this in your query:

  UPDATE
    TableA
  SET
    Test = Left("longstring",FieldSize("TableA","Test"));

However, DAO SQL seems to do an automatic truncation as this
simplified code neither will fail:

  UPDATE
    TableA
  SET
    Test = "longstring";

Thus your problem may be a Unicode issue. Have you looked at the
StrConv() function and the vbUnicode/vbFromUnicode constants?

/gustav


> I understand what you are saying.  Now the question becomes, how can
> I determine how many ASCII characters are being returned?  When I
> try using LEN(<<Field Name>>) in A2K it returns 255.  I have not
> tried it using the Query Analyzer in SQL Server yet.

>         Subject:        RE: [AccessD] Error Message: The field is to
> small to accept the amount of data you tried to add. Try inserting
> or pasting less data 


> Well I count 258 characters in the data, so the error message is
> totally on the mark (odd for Access<g>).

> I suspect that SQL server is passing back all 258 ASCII characters,
> which is why A97 chokes. But A2K is Unicode character savvy, so I
> think it can handle 510 ASCII characters, or 255 Unicode ones.