Results 1 to 6 of 6
  1. #1
    RogerD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2014
    Location
    Ky
    Posts
    27

    Property Value Too Large in a Crosstab Query

    We are using an Access Database that was written several years ago. The person who originally wrote the database is no longer here. It has been updated to the latest db version over the years as/when necessary.
    Over the weekend, the db increased in size from 11 Meg to 233 Meg. It was brought to my attention the users were having issues this morning, and that is when I discovered the increase in size.. I compacted and repaired, and size dropped from 233 meg to 11.5 meg.
    But when we run the query referenced in the sql view below, we will intermittently receive an error message "Property Value too Large".
    on top of the error message, the first column in the query is dropped. We copy the crosstab out of the db and into an Excel spreadsheet using vlookups to summarize data for analysis. dropping a column causes the vlookup to not work.
    Also, we are having problems with permissions, which only occurred intermittently in the past.
    any help would be appreciated!



    TRANSFORM Sum(IncomingRawGlass.PalletQuantity) AS SumOfPalletQuantity
    SELECT IncomingRawGlass.ItemNumber, GlassDetails.Comment, IncomingRawGlass.DeleteRowsNotEntireSheet, IncomingRawGlass.DeleteRowsNotEntireSheet
    FROM (dates2 INNER JOIN IncomingRawGlass ON dates2.dates = IncomingRawGlass.DateDue) INNER JOIN GlassDetails ON IncomingRawGlass.ItemNumber = GlassDetails.[Item#]
    WHERE (((IncomingRawGlass.DateDue)<Date()+"45"))
    GROUP BY IncomingRawGlass.DeleteRowsNotEntireSheet, IncomingRawGlass.ItemNumber, GlassDetails.Comment, IncomingRawGlass.DeleteRowsNotEntireSheet
    PIVOT IncomingRawGlass.DateDue;

  2. #2
    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
    I haven't investigated your sql in detail, but I note that
    Code:
    WHERE (((IncomingRawGlass.DateDue)<Date()+"45"))
    is using a text value of 45 when trying to add days to Date.
    Should be simply + 45 ( a numeric value)

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It would help if you specified what is shown as too large.
    However the WHERE line is wrong.
    Remove the quotes around 45.
    Dates are stored as long integers so you can add numbers to them

    EDIT Orange got there first....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    RogerD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2014
    Location
    Ky
    Posts
    27
    Quote Originally Posted by orange View Post
    I haven't investigated your sql in detail, but I note that
    Code:
    WHERE (((IncomingRawGlass.DateDue)<Date()+"45"))
    is using a text value of 45 when trying to add days to Date.
    Should be simply + 45 ( a numeric value)

    Yes, you are correct. I removed the quotes from "45". ran the query and it ran correctly. Ran it again, and received the error message "Property Value is too large"

  5. #5
    RogerD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2014
    Location
    Ky
    Posts
    27
    There is no specification as to where the error is, just there is an error.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You are selecting and grouping by the same field twice - remove the second occurrence
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Property value is too large
    By NateH in forum Access
    Replies: 6
    Last Post: 12-14-2016, 03:25 PM
  2. Database - Property value too large
    By jenna36 in forum Database Design
    Replies: 1
    Last Post: 08-03-2015, 12:49 PM
  3. Property value is too large
    By Jean Kipling in forum Database Design
    Replies: 3
    Last Post: 01-30-2013, 05:04 PM
  4. Property value is too large.
    By foru in forum Access
    Replies: 9
    Last Post: 06-28-2011, 03:55 PM
  5. Replies: 11
    Last Post: 11-26-2010, 10:53 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