Strange Calculation Error

[AlexCuse]

Hi Guys,

Google is not giving me any luck on this, but I am getting

Reserved error (-1038); there is no message for this error

in one of my DB's (access 2k3, access 2k file format).

Here is the offending query:

SELECT qryQuantity.SOURCE_CODE, qryQuantity.QUANTITY*qryCostPerPiece.CPP AS MEDIA_COST
FROM qryQuantity 
INNER JOIN qryCostPerPiece ON qryQuantity.LISTS=qryCostPerPiece.LIST;

Here is qryQuantity:

SELECT SOURCE_INFO.SOURCE_CODE
, SOURCE_INFO.LISTS
, count(MAIL_FILE.FNAME) AS QUANTITY
FROM (MAIL_FILE INNER JOIN SOURCE_INFO ON MAIL_FILE.EASY_REF=SOURCE_INFO.EASY_REF) 
INNER JOIN LIST_COST ON SOURCE_INFO.LISTS=LIST_COST.List
GROUP BY SOURCE_INFO.SOURCE_CODE, SOURCE_INFO.LISTS;

Here is qryCostPerPiece:

SELECT LIST_COST.LIST
, LIST_COST.COST AS COST
, count(MAIL_FILE.FNAME) AS QUANTITY
, LIST_COST.COST/count(MAIL_FILE.FNAME) AS CPP
FROM (MAIL_FILE INNER JOIN SOURCE_INFO ON MAIL_FILE.EASY_REF=SOURCE_INFO.EASY_REF)
INNER JOIN LIST_COST ON SOURCE_INFO.LISTS=LIST_COST.List
GROUP BY LIST_COST.COST, LIST_COST.LIST;

All underlying data types for cost columns are decimal(18,6), and the quantity is an int (I presume that is what count returns). The two underlying queries run just fine.

I greatly appreciate any assistance.

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hey guys,

I gave up on this and decided to just use a SQL Server back-end for this project. This project is not for a very big client, and I was trying to keep it lightweight, but if Jet doesn't want to let me accomplish what I need to do, there's not much point, right?

I would still like to know if anyone has run into this issue, for future reference.

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

In Access 2, -1038 was Jet error, Buffer is too small, is that a possibility?

 

[AlexCuse]

Remou -

It could be. I don't really know Jet's rules on data type precedence, but it could have been allocating only enough space for an int when it needed a decimal? Its' something to look into for sure (now that I have it in SQL server, I don't think I'm bringing it back to access, but it'll be good to know in the future).

I'll let you know what I find out, if anything. Oddly enough, once I put my tables into SQL Server (and created views of course) I was able to run my nested queries just fine (through ODBC linked tables, not pass-through queries)

Curious indeed. I think I'm going to use the views, just because I have little faith in the jet engine making these calculations atm.

Thanks for the reply, have one of those purply thingys

PS - Access 2 huh? You've been doing this for a while I see. No wonder you da man!

Ignorance of certain subjects is a great part of wisdom