the field is too small to accept the amount of data you attempted to add

[ryetee]

I have been amending a report that is only 3 inches wide(!) and is used on a small portable zebra printer

I added 2 new fields. The report worked perfectly except that the balance field was showing a different value.

To track down the problem I had to widen the width of the report and cycle through some controls that were hidden. Once I found the problem, which was basically picking up the old control when I should have been using one of the new fields, I exited the report WITHOUT saving.

I then went back into the report from within the application (no problem) went into design mode and changed the necessary to pick up the right control. I exited and saved.

Now when I go into the report I get the message

"the field is too small to accept the amount of data you attempted to add"

I have tried removing field by field until there were no fields left (ie a blank report) and still getting the error.

Googling this it points to having UniqueValues query property to Yes which generates a DISTINCT keyword in the sql and this is a problem with MEMO fields. Well I don't know in the UniqueValues query property is set to yes but the generated sql has no DISTINCT keyword and I have no MEMO fields.

So to clarify I basically had a report that worked after I had amended it but
after changing contrl3 to pick up details from control2 rather than control1 it screws up. Changing them back has no effect.

H E L P ! ! ! !

 

[sneuberg]

I suggest you create a new report using the record source of this one. That would tell you if the problem is in the record source, albeit based on what you said I don't see how that could have been changed. You can just select the record source and then click on Report in the CREATE Tab. This will only take a few seconds.

 

[ryetee]

I suggest you create a new report using the record source of this one. That would tell you if the problem is in the record source, albeit base on what you said I don't see how that could have been changed. You can just select the record source and then click on Report in the CREATE Tab. This will only take a few seconds.

Ok - record source is a query so I'll give it a go but can't see that being the problem. I did actually change the query but the report was running fine with the changes. Was only the final changed to the form that appeared to cause a he problem.

 

[gemma-the-husky]

are you saving data longer than 255 in a text field - you need a memo field to do that. The most likely cause is a string that's bigger than the storage size.

I don't mean it's too long to see everything, but that the data type can't hold the entire value.

I

 

[The_Doc_Man]

I'm with Dave on this one. That error sounds more like a string-field overflow than a control overflow. Controls USUALLY just bloody truncate your field visually even though the raw data is still there.

 

[sneuberg]

I've tried to recreate the error in the attached database and found that neither inserting more than 255 characters into a short text field nor appending a long text field to a short text field causes this error. I was able to create the error by having the UniqueValues query property to Yes in a query of long text fields.

@ryetee Could you upload your database. I'd really like to see if I can figure this one out.

 

[ryetee]

are you saving data longer than 255 in a text field - you need a memo field to do that. The most likely cause is a string that's bigger than the storage size.

I don't mean it's too long to see everything, but that the data type can't hold the entire value.

I

No I'm not - I'm running a query that has always worked.
All I've done is add 2 new controls on to the report - amount of tax and grand total. This works. The balance field was wrong as it was picking up the total and not grand total. I then made the change to correct this and this is when I started getting the error. I undid the change and still got the error. I removed every field from the report 1 by 1 and still get the error.

Should I be looking at the query?

 

[ryetee]

I'm with Dave on this one. That error sounds more like a string-field overflow than a control overflow. Controls USUALLY just bloody truncate your field visually even though the raw data is still there.

See above but removing all controls still leaves me with the error.

 

[ryetee]

I've tried to recreate the error in the attached database and found that neither inserting more than 255 characters into a short text field nor appending a long text field to a short text field causes this error. I was able to create the error by having the UniqueValues query property to Yes in a query of long text fields.

@ryetee Could you upload your database. I'd really like to see if I can figure this one out.

How do you change the UniqueValues query property or at least see if it's set?

Not sure my employer will allow me to upload - I'll find out what I can do with that regard.

 

[sneuberg]

You can right click in a empty part of the grid as shown below to see the query properties. Note that if you click in a part of the grid that has a field you will get the field properties instead. You can also look at the query in SQL view and see if it has the DISTINCT keyword.

Have you tried to create a new report with the record source query? Just three seconds and you'd know whether you need to be looking at the query.

 

[sneuberg]

Concerning uploading your database if your database has sensitive information consider this. Or create a new database and import just the objects from this database that are part of the problem.

 

[MarkK]

I've tried to recreate the error in the attached database and found that neither inserting more than 255 characters into a short text field nor appending a long text field to a short text field causes this error.

An insert query in DAO appears to truncate string data to the size of the field before attempting to insert it. To cause the error reported by the op, add the data via a recordset, like...

Sub InsertBigData()

Dim BigString As String

BigString = "0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF"

CurrentDb.Execute "DELETE * FROM Table1"
BigString = BigString & BigString
Debug.Print "Initial Sizs:  " & Len(BigString)
CurrentDb.Execute "INSERT INTO [Table1] ([F1]) VALUES ( '" & BigString & "')", dbFailOnError
Debug.Print "Final Size: " & Len(DLookup("[F1]", "[Table1]"))

    With CurrentDb.OpenRecordset("Table1")
        .AddNew
        !f1 = BigString[COLOR="Green"] 'error occurs here[/COLOR]
        .Update
        .Close
    End With

End Sub

 

[ryetee]

You can right click in a empty part of the grid as shown below to see the query properties. Note that if you click in a part of the grid that has a field you will get the field properties instead. You can also look at the query in SQL view and see if it has the DISTINCT keyword.



Have you tried to create a new report with the record source query? Just three seconds and you'd know whether you need to be looking at the query.

Spotted the query properties and mine is set to "no".

Not tried creating report yet as it looks like there is a sub report that has some weird linking of master and child fields.

Also spotted that this only seems to work with one specific order. I'm trying to recreate the problem by trying to input identical order and then drilling down from there to see where problem lies.

 

[ryetee]

Concerning uploading your database if your database has sensitive information consider this. Or create a new database and import just the objects from this database that are part of the problem.

Very interesting. I'll use that in future but I've now found what data is causing the problem. Just need to work out where it's actually used to cause the problem! Must be in a query.

 

[sneuberg]

You might want to look at the fields that are joined in the queries to make sure they are the same size. Usually text fields are left at the default of 255 but I they can be set to lesser values. I also recall different sizes causing problems in a join, although I can't remember if it produced this error.

 

[ryetee]

Seems like this report also updates a field on a table by concatenating 2 fields together. The size of the field is 50 characters so if you run the report a number of times you blow the 50 characters.

 

[sneuberg]

A report updating a table? That's has to be the strangest thing I've heard of on this forum. Do you know why this is being done?

 

[ryetee]

A report updating a table? That's has to be the strangest thing I've heard of on this forum. Do you know why this is being done?

The whole application does some weird and wonderful things. I should have checked the code first before looking at the report itself and underlying queries.

It looks like it's some audit trail - but you can bet that it's never ever used elsewhere. The report is actually a receipt and you can make partial payments. Usually the payment is in full but if they have more than something like 4 partial payments then this error will appear as the date of payment is stored.

 

[gemma-the-husky]

change the field type to a memo, is the simplest way to fix this.

 

[ryetee]

change the field type to a memo, is the simplest way to fix this.

There are multiple instances of the database so I think I'm either going to leave the error (the likelihood of this happening in 'real' life is slim and it doesn't affect the report anyway) or have some code that checks the length of the field before update and ignores if it's above a certain amount.