Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85

    What does this error message mean? Part TWO


    Well I figured out what was wrong with a previous message. Now, when I try to open a large report, I'm getting the following error. I have no idea what field it is referring to! Any way to track it down?

    Click image for larger version. 

Name:	FieldTooSmall.jpg 
Views:	39 
Size:	14.7 KB 
ID:	38458

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  3. #3
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  5. #5
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    Just wondering why it's worked for 8 years and is now giving me fits.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Posting a copy of your database with enough info to show the issue would help readers understand things in context.

  7. #7
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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.

  8. #8
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    Wish I could post a copy of the database, but my employer doesn't allow that.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is difficult to understand your situation in any detail without seeing the code.
    You said
    Just wondering why it's worked for 8 years and is now giving me fits.
    My first question would be --what has changed? Did you modify a table, a process...?
    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?

  10. #10
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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.

  11. #11
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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;

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you are looking for your culprit, it'll be any field with a LONG TEXT or MEMO data type.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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

  15. #15
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    No integer fields. All Double, Standard. I'll keep trying.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  2. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  3. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  4. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  5. sql error when day part of date starts with a zero
    By OceanaPolynom in forum Programming
    Replies: 4
    Last Post: 04-01-2013, 02:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums