If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"The field is too small..." error coming up on Select query.
I have a SELECT SQL query in Access 2003 that throws the error "The field is
too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(Batting.FPTS) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(Pitching.FPTS) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, BScores.BPTS+PScores.PPTS AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#2
|
|||
|
|||
"The field is too small..." error coming up on Select query.
One possibility would be that the sum of, for example, Batting.FPTS might potentially be a much larger number than any of the individual values in the column. JET may be seeing that the data type of Batting.FPTS is, say, Single, and assuming that the data type of the expression Sum(Batting.FPTS) can also be Single, where possibly it may need to be Double. You could try, for example, Sum(CDbl(Batting.FPTS)). You also have some field names that are either reserved words or names of functions, such as Year and Name. I wouldn't expect that to cause this particular problem, but it wouldn't hurt to put square brackets around your field names just in case, e.g. Batting.[Year] and Teams.[Name]. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(Batting.FPTS) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(Pitching.FPTS) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, BScores.BPTS+PScores.PPTS AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#3
|
|||
|
|||
"The field is too small..." error coming up on Select query.
That did seem to be on the right path. The ScoreSched table doesn't throw the
error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried adding CDbl() to all the PTS columns in each table, but I'm not exactly sure if I'm doing it right. I've modified the queries below how i've modified them in Access, and it still doesn't work (again, it's only ScoreSched that throws an error): Any idea what I'm doing wrong? "Brendan Reynolds" wrote: One possibility would be that the sum of, for example, Batting.FPTS might potentially be a much larger number than any of the individual values in the column. JET may be seeing that the data type of Batting.FPTS is, say, Single, and assuming that the data type of the expression Sum(Batting.FPTS) can also be Single, where possibly it may need to be Double. You could try, for example, Sum(CDbl(Batting.FPTS)). You also have some field names that are either reserved words or names of functions, such as Year and Name. I wouldn't expect that to cause this particular problem, but it wouldn't hurt to put square brackets around your field names just in case, e.g. Batting.[Year] and Teams.[Name]. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(CDbl(Batting.FPTS)) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(CDbl(Pitching.FPTS)) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, CDbl(BScores.BPTS)+CDbl(PScores.PPTS) AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#4
|
|||
|
|||
"The field is too small..." error coming up on Select query.
Are you dealing with very large numbers? What are the largest values in BScores.BPTS and PScores.PPTS? -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... That did seem to be on the right path. The ScoreSched table doesn't throw the error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried adding CDbl() to all the PTS columns in each table, but I'm not exactly sure if I'm doing it right. I've modified the queries below how i've modified them in Access, and it still doesn't work (again, it's only ScoreSched that throws an error): Any idea what I'm doing wrong? "Brendan Reynolds" wrote: One possibility would be that the sum of, for example, Batting.FPTS might potentially be a much larger number than any of the individual values in the column. JET may be seeing that the data type of Batting.FPTS is, say, Single, and assuming that the data type of the expression Sum(Batting.FPTS) can also be Single, where possibly it may need to be Double. You could try, for example, Sum(CDbl(Batting.FPTS)). You also have some field names that are either reserved words or names of functions, such as Year and Name. I wouldn't expect that to cause this particular problem, but it wouldn't hurt to put square brackets around your field names just in case, e.g. Batting.[Year] and Teams.[Name]. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(CDbl(Batting.FPTS)) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(CDbl(Pitching.FPTS)) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, CDbl(BScores.BPTS)+CDbl(PScores.PPTS) AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#5
|
|||
|
|||
"The field is too small..." error coming up on Select query.
1484 is the largest value for those fields. 2412 is the largest value for the
sum used in TScores.FPTS. "Brendan Reynolds" wrote: Are you dealing with very large numbers? What are the largest values in BScores.BPTS and PScores.PPTS? -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... That did seem to be on the right path. The ScoreSched table doesn't throw the error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried adding CDbl() to all the PTS columns in each table, but I'm not exactly sure if I'm doing it right. I've modified the queries below how i've modified them in Access, and it still doesn't work (again, it's only ScoreSched that throws an error): Any idea what I'm doing wrong? "Brendan Reynolds" wrote: One possibility would be that the sum of, for example, Batting.FPTS might potentially be a much larger number than any of the individual values in the column. JET may be seeing that the data type of Batting.FPTS is, say, Single, and assuming that the data type of the expression Sum(Batting.FPTS) can also be Single, where possibly it may need to be Double. You could try, for example, Sum(CDbl(Batting.FPTS)). You also have some field names that are either reserved words or names of functions, such as Year and Name. I wouldn't expect that to cause this particular problem, but it wouldn't hurt to put square brackets around your field names just in case, e.g. Batting.[Year] and Teams.[Name]. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(CDbl(Batting.FPTS)) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(CDbl(Pitching.FPTS)) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, CDbl(BScores.BPTS)+CDbl(PScores.PPTS) AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#6
|
|||
|
|||
"The field is too small..." error coming up on Select query.
I'm afraid I'm at a loss, then. Those numbers should fit perfectly happily
into an Integer, let alone a Double. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... 1484 is the largest value for those fields. 2412 is the largest value for the sum used in TScores.FPTS. "Brendan Reynolds" wrote: Are you dealing with very large numbers? What are the largest values in BScores.BPTS and PScores.PPTS? -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... That did seem to be on the right path. The ScoreSched table doesn't throw the error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried adding CDbl() to all the PTS columns in each table, but I'm not exactly sure if I'm doing it right. I've modified the queries below how i've modified them in Access, and it still doesn't work (again, it's only ScoreSched that throws an error): Any idea what I'm doing wrong? "Brendan Reynolds" wrote: One possibility would be that the sum of, for example, Batting.FPTS might potentially be a much larger number than any of the individual values in the column. JET may be seeing that the data type of Batting.FPTS is, say, Single, and assuming that the data type of the expression Sum(Batting.FPTS) can also be Single, where possibly it may need to be Double. You could try, for example, Sum(CDbl(Batting.FPTS)). You also have some field names that are either reserved words or names of functions, such as Year and Name. I wouldn't expect that to cause this particular problem, but it wouldn't hurt to put square brackets around your field names just in case, e.g. Batting.[Year] and Teams.[Name]. -- Brendan Reynolds Access MVP "Mayhew" wrote in message ... I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: BScores: SELECT Batting.Team, Batting.Period, Batting.Year, Sum(CDbl(Batting.FPTS)) AS BPTS FROM Batting WHERE Status='A' GROUP BY Team, Period, Year; PScores: SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(CDbl(Pitching.FPTS)) AS PPTS FROM Pitching WHERE Status='A' GROUP BY Year, Team, Period; TScores: SELECT BScores.Team, BScores.Period, BScores.Year, CDbl(BScores.BPTS)+CDbl(PScores.PPTS) AS FPTS FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND (BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year); ScoreSched (this query throws the error): SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name FROM TScores, Teams WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr; If I copy the data from TScores into Excel and then into a straight Access table, the query works fine joining that table with Teams. I am hitting my head against the wall on this, it makes no sense to me. The only thing the error implies to me is that there is some data type mismatch, but all the referenced data types are exactly the same, though referenced through multiple layers of queries. Please help! |
#7
|
|||
|
|||
"The field is too small..." error coming up on Select query.
I have a similar problem with a twist. I just converted an Access 97
application to Access 2003 and started seeing this message. It is a union query in which each piece works fine independently. This error message is worthless. Mayhew wrote: I have a SELECT SQL query in Access 2003 that throws the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I am not inserting or pasting anything. It is a query based on other queries, however, but I can't find why it doesn't work. Here are the SQL statements for the queries. "Batting", "Pitching", and "Teams" are tables, with pretty simple text and number data: |
Thread Tools | |
Display Modes | |
|
|