Results 1 to 5 of 5
  1. #1
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20

    Access Error


    I'm wondering if anyone can shed some light on an error I was getting when running a select query. The error read "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." My query uses grouping as well as one column as criteria (using "where"). The following criteria worked for a full year...<> "FINAL TOTAL". When I reset the year (deleted last year's data and added this year's data in the main table of the query), I started getting that error. I finally figured out that if I changed it to < "FINAL TOTAL" OR > "FINAL TOTAL", it works. Any ideas why that would have changed and aren't both of the ways I wrote the criteria the same thing?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is "Final Total", literal text that goes into the field of a record or is that the name of your field?
    The error message should have nothing to do with using < or <>. It means that the data you are trying to input exceeds what the field can hold - in at least one record of the query. However, a SELECT query cannot append or update data so that makes no sense to me. Maybe you have an open query and you're trying to edit or append with it directly?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    "FINAL TOTAL" was the value in a field named Product Number that I was trying to exclude in the results. I switched my criteria field to Key and am using "All" (which represents the same records that have FINAL TOTAL for the product number). When using <>, it gives me the error, but works when I change it to < "All" OR >"All". And, again, this is a SELECT query. Incidentally, it does actually give me the same error when I use the Product Number field and "FINAL TOTAL" as the criteria regardless of how I write the criteria (both <> and <OR> give me the error when using "FINAL TOTAL" in the Product Number field), but worked last year. I misspoke in my original post.

    Doesn't work: <>"All"
    Works: >"All" Or <"All"






  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Corruption is the only thing that comes to mind at this point. You could try NOT LIKE "FINAL TOTAL" but I can't think of why <> raises an error about the field being too small. You could copy/compact and zip your db and upload here if you want someone to look into it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post the SQL of the query??

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: 3
    Last Post: 05-12-2016, 07:06 PM
  3. Replies: 6
    Last Post: 03-17-2016, 02:10 PM
  4. Replies: 3
    Last Post: 02-26-2016, 12:34 PM
  5. Replies: 6
    Last Post: 09-28-2011, 09:20 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