Error 3163: The field is too small to accept the amount of data you're tryng to add (1 Viewer)

A

Alc

Registered User.
Local time
Today, 00:57
Joined
Mar 23, 2007
Messages
2,407
I have the following query, which runs perfectly:
Code: 
SELECT 
[qry_BMO_District_Recap - RHS - Stage 2].Transit, 
Format([qry_BMO_District_Recap - RHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear, 
[qry_BMO_District_Recap - RHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority, 
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount, 
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount, 
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount, 
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount, 
[qry_BMO_District_Recap - RHS - Stage 2].AREANAME, [qry_BMO_District_Recap - RHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - RHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - LHS - Stage 2] 
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
When I try to reverse the above, as shown
Code: 
SELECT 
[qry_BMO_District_Recap - LHS - Stage 2].Transit, 
Format([qry_BMO_District_Recap - LHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear, 
[qry_BMO_District_Recap - LHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority, 
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount, 
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount, 
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount, 
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount, 
[qry_BMO_District_Recap - LHS - Stage 2].AREANAME, [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - LHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - RHS - Stage 2] 
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority) 
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
I get the error message "The field is too small to accept the amount of data you are trying to add. try inserting or pasting less data." I'm not attempting to run an insert or update query.

Access Help lives up to expectations by just repeating the error message, and the posts I can find on the forum which mention this error all seem to include something about a maximum of 254 characters in a field. None of mine are anywhere near that amount.

Does anyone have any idea what could be causing this?
 
RuralGuy

RuralGuy

AWF VIP
Local time
Yesterday, 22:57
Joined
Jul 2, 2005
Messages
13,826
You can get that error if you try and put decimal data into an integer field but you don't get it if you put integer data into a decimal field.
 
A

Alc

Registered User.
Local time
Today, 00:57
Joined
Mar 23, 2007
Messages
2,407
RuralGuy said:
You can get that error if you try and put decimal data into an integer field but you don't get it if you put integer data into a decimal field.
Click to expand...
Thanks for that.
When you talk about putting data in, what exactly does that mean? Displayng my ignorance, I thought a select query just looked up data.
 
A

Alc

Registered User.
Local time
Today, 00:57
Joined
Mar 23, 2007
Messages
2,407
I'd rather it had been invalid but found a solution.

Sorted! By a process of elimination - coupled with the fact that as soon as you ask someone else, you always work it out - I narrowed it down to the fact that the Transit field I was using to link the two queries was an integer in one and text in another (don't ask why, I just inherited it). Using Val() to perform the link appears to have done the trick. :)
 
RuralGuy

RuralGuy

AWF VIP
Local time
Yesterday, 22:57
Joined
Jul 2, 2005
Messages
13,826
Excellent! Thanks for posting back with your success.
 
S

sjwtcil

New member
Local time
Today, 05:57
Joined
Jan 20, 2009
Messages
1
Here is a strange one! I am getting the same error message (3163) when using a new laptop to run a form that updates a table in a 97 access database, the strange thing is it runs fine on 5 other PC's and one other laptop. If the problem was the different field types why does it work on other machines?

Any suggestions would be greatly received.

Many thanks

Steve
 
C

crapanz

New member
Local time
Yesterday, 21:57
Joined
Dec 20, 2013
Messages
1
I had this same issue in a MS Access 2000 front-end file format, running in MS Access 2003.

The back-end, obviously a split database was in Access 2000 format as well.

I was getting other errors too, like weird values upon query execution, but I seemed to circumvent them. I was getting a invoice sum over 5,000,000 when it should have been about a few hundred dollars.

But when I ran into this field to small error, I had big difficulties, since I could not run queries I had run before!
I traced this whole difficulty to a front-end that was not compacted and repaired.
I had been working on the front-end to add more features.
I usually compact and repair it before using it again, ad the front end gets bloated.
I also had recently added some fields to my back-end.

This is very shocking cause :eek: it has nothing to do with the data at all, but obviously there was some interaction.
Whatever garbage uncleaned up was created in the front-end, and interfered with the query I have no idea!

If anyone can explain this to me, I would appreciate it.
Also I wonder if this error happens in MSAccess 2007-2010, but it may be the backend access format that is the issue.

Anyways, I hope this helps someone else since this is not obvious and does not seem to be on the MSAccess knowledge base.

Incidentally I went back and fixed the database, by entering only a few records.
I compact and repair my front-end and back-end periodically.:cool:
 
You must log in or register to reply here.

Similar threads

H
The decimal field's precision is too small to accept the numeric you attempted to add
Replies
7
Views
6,343
isladogs
isladogs
R
the field is too small to accept the amount of data you attempted to add
2
Replies
25
Views
8,666
The_Doc_Man
The_Doc_Man
T
"Query too complex" error from Concatenation(?)
Replies
7
Views
2,281
thardyjackson
T
A
The field is too small to accept the amount of data you attempted to add
Replies
1
Views
5,052
DCrake
DCrake
A
Error: Field is too small to accept amount of data
Replies
14
Views
16,667
MarkK
MarkK

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom