likely it is a text field (max 255 characters, I believe) although you could have set this field to hold less in table field properties. That is, you could set it to a max of 10 characters or whatever you like that is less than the max. I don't think it's because a number you're trying to insert is too large. I think but am not positive but I think that would be a different message. Try running the query that the report is based on and see what happens. If nothing, then the issue should be with a report control. You'd have to test to figure out which one, or look for any restriction you have imposed.
Last edited by Micron; 05-21-2019 at 12:16 PM. Reason: spelin and gramur
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Turns out the problem is with my query, I get the same error message when I try to run that query. Now just have to figure out why!
if your dataset contains a long text field it's very easy to produce this error. Especially if you're trying to perform an operation on it like having a criteria.
Just wondering why it's worked for 8 years and is now giving me fits.
Posting a copy of your database with enough info to show the issue would help readers understand things in context.
Here's the deal. It appears all my tables, if they have text fields, are set to short text with 255 as the length. This has never been an issue with this query before, and every one of my other queries that uses the same tables runs perfectly. That indicate a problem with a field IN the query itself correct? Also, could there be a problem with a number field causing this? Most of the fields are number fields, not text fields. Just trying to narrow this down.
Wish I could post a copy of the database, but my employer doesn't allow that.
It is difficult to understand your situation in any detail without seeing the code.
You said
My first question would be --what has changed? Did you modify a table, a process...?Just wondering why it's worked for 8 years and is now giving me fits.
Do you get an Error message/number?
I am not asking for details or confidential material -- you can change names of people, cities, amounts to dummy values - Porky Pig, Centerville, ... we just need to see what you are trying to do and how you have approached your solution.
We often mock up a situation to avoid personal and/or confidential info.
If something has run without error for 8 years, then errors --something has changed:
-application/records
-software
-hardware
-network
-user
?????
You do realize that XP and 2003 are dated, right?
I am painfully aware of how dated XP and 2003 are. I am trying to use a 2003 database in Access 2016 with Windows 10. So far, I've been able to limp along. Unfortunately, it appears I can't just upgrade the database to a newer version of Access without completely reconstructing the database and 8 years of data. If you have suggestions for that I'm all ears. Oh, and I retire on August 8.
For what it's worth, this is the code for the query in SQL. I copied this into Word and went line by line, so the code appears to be correct, I just need to find out which one is no big enough to hold the data being delivered.
SELECT SeasonalInfoByPlanting.Season, SeasonalInfoByPlanting.Location, SeasonalInfoByPlanting.Planting, SeasonalInfoByPlanting.VegetableGroup, SeasonalInfoByPlanting.VegetableType, Max(HarvestingLog.Pick) AS [Last Pick], SeasonalInfoByPlanting.SumOfAcresPlanted, Min(HarvestingLog.Date) AS [Date Begin Pick], Max(HarvestingLog.Date) AS [Date Last Pick], Sum(HarvestingLog.GRPCS) AS TotalSumOfGRPCS, Sum(HarvestingLog.Tubs) AS TotalSumOfTubs, Sum(HarvestingLog.UgRPCS) AS TotalSumOfUgRPCS, Sum(HarvestingLog.CuRPCS) AS TotalSumofCuRPCS, Sum(HarvestingLog.CuBoxes) AS TotalSumofCuBoxes, Sum(HarvestingLog.SqRPCS) AS TotalSumOfSqRPCS, Sum(HarvestingLog.SqBoxes) AS TotalSumofSqBoxes, Sum(HarvestingLog.Bins) AS TotalSumOfBins, Sum(HarvestingLog.PprRPCS) AS TotalSumOfPprRPCS, Sum(HarvestingLog.PprBoxes) AS TotalSumOfPprBoxes, Sum(HarvestingLog.LBS) AS TotalSumOfLBS, Sum(HarvestingLog.RomaBins) AS TotalSumOfRomaBins, Sum(HarvestingLog.RomaLBS) AS TotalSumOfRomaLBS, Sum(HarvestingLog.RdRPCS) AS TotalSumOfRdRPCS, Sum(HarvestingLog.RomaRPCS) AS TotalSumOfRomaRPCS, Sum(HarvestingLog.CherryRPCS) AS TotalSumOfCherryRPCS, Sum(HarvestingLog.ZuRPCS) AS TotalSumOfZuRPCs, Sum(HarvestingLog.ZuBoxes) AS TotalSumOfZuBoxes, Max(LatestRPCWeightsInformationQuery.WtGRPCS) AS MaxOfWtGRPCS, Max(LatestRPCWeightsInformationQuery.WtUgRPCS) AS MaxOfWtUgRPCS, Max(LatestRPCWeightsInformationQuery.WtRdRPCS) AS MaxOfWtRdRPCS, Max(LatestRPCWeightsInformationQuery.WtRomaRPCS) AS MaxOfWtRomaRPCS, Max(LatestRPCWeightsInformationQuery.WtCherryRPCS) AS MaxOfWtCherryRPCS, Max(LatestRPCWeightsInformationQuery.WtCuRPCS) AS MaxOfWtCuRPCS, Max(LatestRPCWeightsInformationQuery.WtCuBoxes) AS MaxOfWtCuBoxes, Max(LatestRPCWeightsInformationQuery.WtSqRPCS) AS MaxOfWtSqRPCS, Max(LatestRPCWeightsInformationQuery.WtSqBoxes) AS MaxOfWtSqBoxes, Max(LatestRPCWeightsInformationQuery.WtZuBoxes) AS MaxOfWtZuBoxes, Max(LatestRPCWeightsInformationQuery.WtZuRPCS) AS MaxOfWtZuRPCS, Max(LatestRPCWeightsInformationQuery.WtTubs) AS MaxOfWtTubs, Max(LatestRPCWeightsInformationQuery.WtBins) AS MaxOfWtBins, Max(LatestRPCWeightsInformationQuery.WtLBS) AS MaxOfWtLBS, Max(LatestRPCWeightsInformationQuery.WtRomaBins) AS MaxOfWtRomaBins, Max(LatestRPCWeightsInformationQuery.WtRomaLBS) AS MaxOfWtRomaLBS, SeasonalAcreageQuery.SeasonalAcresPlanted, Max(LatestRPCWeightsInformationQuery.WtPprRPCS) AS MaxOfWtPprRPCS, Max(LatestRPCWeightsInformationQuery.WtPprBoxes) AS MaxOfWtPprBoxes, Max(LatestRPCWeightsInformationQuery.WtWSqRPCS) AS MaxOfWtWSqRPCS, Max(LatestRPCWeightsInformationQuery.WtWSqBoxes) AS MaxOfWtWSqBoxes, Sum(HarvestingLog.WSqRPCS) AS TotalSumOfWSqRPCS, Sum(HarvestingLog.WSqBoxes) AS TotalSumOfWSqBoxes, LastFullPickQuery.MinOfDate, LastFullPickQuery.MaxOfDate, LastFullPickQuery.SumOfGRPCS, LastFullPickQuery.SumOfUgRPCS, LastFullPickQuery.SumOfRdRPCS, LastFullPickQuery.SumOfRomaRPCS, LastFullPickQuery.SumOfCherryRPCS, LastFullPickQuery.SumOfCuRPCS, LastFullPickQuery.SumOfCuBoxes, LastFullPickQuery.SumOfSqRPCS, LastFullPickQuery.SumOfSqBoxes, LastFullPickQuery.SumOfZuRPCS, LastFullPickQuery.SumOfZuBoxes, LastFullPickQuery.SumOfTubs, LastFullPickQuery.SumOfBins, LastFullPickQuery.SumOfLBS, LastFullPickQuery.SumOfRomaBins, LastFullPickQuery.SumOfRomaLBS, LastFullPickQuery.SumOfPprRPCS, LastFullPickQuery.SumOfPprBoxes, LastFullPickQuery.SumOfWSqRPCS, LastFullPickQuery.SumOfWSqBoxes
FROM LatestRPCWeightsInformationQuery, LastFullPickQuery INNER JOIN (SeasonalAcreageQuery INNER JOIN (HarvestingLog INNER JOIN SeasonalInfoByPlanting ON (HarvestingLog.Season = SeasonalInfoByPlanting.Season) AND (HarvestingLog.Location = SeasonalInfoByPlanting.Location) AND (HarvestingLog.Planting = SeasonalInfoByPlanting.Planting)) ON (SeasonalAcreageQuery.Location = SeasonalInfoByPlanting.Location) AND (SeasonalAcreageQuery.Season = SeasonalInfoByPlanting.Season) AND (SeasonalAcreageQuery.VegetableGroup = SeasonalInfoByPlanting.VegetableGroup)) ON (LastFullPickQuery.Planting = HarvestingLog.Planting) AND (LastFullPickQuery.Season = HarvestingLog.Season)
GROUP BY SeasonalInfoByPlanting.Season, SeasonalInfoByPlanting.Location, SeasonalInfoByPlanting.Planting, SeasonalInfoByPlanting.VegetableGroup, SeasonalInfoByPlanting.VegetableType, SeasonalInfoByPlanting.SumOfAcresPlanted, SeasonalAcreageQuery.SeasonalAcresPlanted, LastFullPickQuery.MinOfDate, LastFullPickQuery.MaxOfDate, LastFullPickQuery.SumOfGRPCS, LastFullPickQuery.SumOfUgRPCS, LastFullPickQuery.SumOfRdRPCS, LastFullPickQuery.SumOfRomaRPCS, LastFullPickQuery.SumOfCherryRPCS, LastFullPickQuery.SumOfCuRPCS, LastFullPickQuery.SumOfCuBoxes, LastFullPickQuery.SumOfSqRPCS, LastFullPickQuery.SumOfSqBoxes, LastFullPickQuery.SumOfZuRPCS, LastFullPickQuery.SumOfZuBoxes, LastFullPickQuery.SumOfTubs, LastFullPickQuery.SumOfBins, LastFullPickQuery.SumOfLBS, LastFullPickQuery.SumOfRomaBins, LastFullPickQuery.SumOfRomaLBS, LastFullPickQuery.SumOfPprRPCS, LastFullPickQuery.SumOfPprBoxes, LastFullPickQuery.SumOfWSqRPCS, LastFullPickQuery.SumOfWSqBoxes;
if you are looking for your culprit, it'll be any field with a LONG TEXT or MEMO data type.
My guess is that only the author of this would have any hope of understanding the SQL and the context.
I retired a number of years ago, and had a successor who was trained in the databases we used. If this database is important to your employer, then I would encourage him/her/them to get someone else trained in the database and related procedures ASAP (August will be here sooner than you may think).
Most forums will provide advice/assistance where possible and appropriate. But when dealing with the type of SQL you provided, we'll need more info; a sample database to highlight the issue and show the relevant queries.
I have seen others whose employer/policy prevents their posting databases. When informed of mock data, mock names etc, there is usually a recognition that -to get help, I must provide info and context and they do. I doubt that people on the forum can guess adequately to offer assistance to you.
I still think the most efficient way to get assistance is to provide a database --same structure, but dummy (anonymized) data sufficient to highlight the problem. Maybe a request to the employer would be helpful.
Good luck with your project -and retirement.
I see rpeare has responded and has suggested the likely suspect.
I doubt anyone will focus much on your query sql - it's far too much and without access to underlying fields, impossible to relate to.
Sure, it could be a number issue (I think). The standard way to trouble shoot something like this is on a query copy, remove a field one at a time until something works. That won't guarantee you have found the issue as it could be that you start over, remove that field and find it doesn't work again. This can happen because it's not one field that's an issue, but a combination of two or more. Since you are doing so many calculations, it may be that a result is too large for the number field, although I'm not positive that the error message would be the same. 33,000 won't fit into an integer field, for example.
Last edited by Micron; 05-22-2019 at 05:41 PM. Reason: clarification
No integer fields. All Double, Standard. I'll keep trying.