Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Ocean Gate, New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query error message (A2K)

    Ina crosstab query when i am trying to populate with sums the query works ok . If i try to use average in this query i get the following error message "Scaling of decimal value resulted in data truncation". Using help does not display any useful information.

    Has anyone ever heard of this before and if so how do I correct?

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: crosstab query error message (A2K)

    What is the data type of the field you are trying to average?

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Ocean Gate, New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query error message (A2K)

    Thank you Hans for the speedy reply.

    I have customers billed monthly for water usage in gallons. I have totaled all gallons billed by water meter size and would now like to average each months consumption by size.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: crosstab query error message (A2K)

    Thanks, but I repeat, what is the data type? Open the table behind the query in design view, and see what is listed in the Data Type column for the water usage field.

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Ocean Gate, New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query error message (A2K)

    Hans,

    I apoligize for misunderstanding your last question.

    Data Type is number, Field Size is decimal.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: crosstab query error message (A2K)

    Why are you using a decimal datatype? In all the years I've been developing in Access, I've never yet had a need for that data type. If you want to work with decimal values, a single or a double would probably be a better choice.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: crosstab query error message (A2K)

    Decimal is not a native Access type. Although it is allowed, Access doesn't treat it correctly. If you set this field type in a table in the database itself, change it to Double, or Currency. If it is a linked table (for example to a table in a SQL Server database), that is not an option. Try using CDbl([FieldName]) instead of FieldName in calculated expressions.

  8. #8
    Star Lounger
    Join Date
    Sep 2003
    Location
    Ocean Gate, New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query error message (A2K)

    Thank - You Hans and Charlotte.

    This table was imported and I have discovered sometimes on imported tables the data type changes. I am sure this was the case here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •