Overflow error

[Thistle]

Hello



I've written a query, and get the following error when I try to execute it:



This is the SQL code that is identical to other queries that work. The only difference is the LineMargin element:

TRANSFORM Sum(qry_202_SalesMargin.LineMargin) AS SumOfLineMargin
SELECT qry_202_SalesMargin.STOCK_CODE, qry_202_SalesMargin.ACCOUNT_REF, qry_202_SalesMargin.Description
FROM qry_202_SalesMargin
GROUP BY qry_202_SalesMargin.STOCK_CODE, qry_202_SalesMargin.ACCOUNT_REF, qry_202_SalesMargin.Description
PIVOT Format([INVOICE_DATE],"yyyy-mm");

This is my first post in the forum. ANy ideas would be appreciated.

Thistle

[ItsMe]

I cannot see the image of the actual error, but if it is an overflow error, the first thing I would wonder is what is the data type of the LineMargin column.

[Thistle]

Thanks for responding.

I snipped and pasted the image of the error windows, but it's not showing in the note. All it said was "Overflow".

The LineMargin colum is Standard and 2 decimal places.

Cheers

[ItsMe]

I am not familiar with the Standard data type. Perhaps a better fit would be the Number data type with a field size of Double.

[Thistle]

My knowledge of Access is limited. The options given for the format of the number are: General number, Currency, Euro, Fixed, Standard, Percent, Scientific. I chose Standard with 2 decimal places.

[ItsMe]

My knowledge of Access is limited. The options given for the format of the number are: General number, Currency, Euro, Fixed, Standard, Percent, Scientific. I chose Standard with 2 decimal places.

The format property is used to determine how data is displayed to the User. It does not affect the underlying data that is stored in the table. The format property only determines how stuff appears.

What I am referring to is the actual data type. While in design view of your table, select the data type of your field. Apparently, the LineMargin filed is already type Number. Now, look at the properties of this LineMargin field that is of type Number. Towards the bottom of the window of Design View, there are the properties. Under the General tab, you will see a "Field Size" property. Make this bigger, maybe Double or Decimal.

[Thistle]

Many thanks indeed for the explanation.

As luck would have it, with the help of an Access Developer friend who happened to be in town today, I discovered that there were three invoices raised in the period, out of thousands, that had line items with zero quantity and zero revenue. I deleted these three empty line items from the Access tabkle that I'd imported into (from Sage Accounting), and then my queries worked a treat.

Once agin, thanks for your response.

Cheers