By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Error on query calculation

P: 10
I am a novice working in Access 2002 trying to run a query I created. I am receiving the error "scaling of decimal value resulted in data trunction", which I have determined is due to this calculation: I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS

My query includes 6 joins but it happens with just 1 join or all of them. If I pull this calculation out, it works fine. In design view, I have tried to adjust the decimal property to every possible value from Auto to 7, and nothing seems to work. I still get the error when I run the query with the joins. Here is my query with just 1 of the 6 joins:

SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, T.ENROLLMENTSTATUSDESCR
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTFULL AS T
WHERE (((T.LEARNINGACTIVITYID)=[i].[LEARNINGACTIVITYID]) AND ((T.INSTANCECODE)=[i].[code]) AND ((I.LEARNINGACTIVITYCODE) Like 'ODL%') AND ((I.STARTDATE) Between #9/1/2006# And #9/30/2006#) AND ((I.STATUS)='A') AND ((T.ENROLLMENTSTATUS)='C') AND ((T.ENROLLMENTSTATUSDESCR)='Completed'))
GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, T.ENROLLMENTSTATUSDESCR, T.ENROLLMENTSTATUS

UNION ALL SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, T.ENROLLMENTSTATUSDESCR
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTFULL AS T
WHERE (((T.LEARNINGACTIVITYID)=[i].[LEARNINGACTIVITYID]) AND ((T.INSTANCECODE)=[i].[code]) AND ((I.LEARNINGACTIVITYCODE) Like 'ODT%') AND ((I.STARTDATE) Between #9/1/2006# And #9/30/2006#) AND ((I.STATUS)='A') AND ((T.ENROLLMENTSTATUS)='C') AND ((T.ENROLLMENTSTATUSDESCR)='Completed'))
GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, T.ENROLLMENTSTATUSDESCR, T.ENROLLMENTSTATUS
ORDER BY I.LEARNINGACTIVITYCODE, I.CODE, I.LEARNINGACTIVITYTITLE, I.STARTDATE DESC;

Any help or suggestions would be greatly appreciated. Thank you.
Sep 21 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00")
Sep 21 '06 #2

P: 10
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00")
Thank you! Do I put it in just like this?

SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00"),
T.ENROLLMENTSTATUSDESCR

I am now getting a syntex error (missing operator) on that code. Also, can I change the decimal to reflect 3 or 4 numbers after the decimal - like 0000.000?
Thanks again.
Sep 21 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
My apologies I was careless

SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS,
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8, "0000.00") AS Days,
T.ENROLLMENTSTATUSDESCR

Thank you! Do I put it in just like this?

SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00"),
T.ENROLLMENTSTATUSDESCR

I am now getting a syntex error (missing operator) on that code. Also, can I change the decimal to reflect 3 or 4 numbers after the decimal - like 0000.000?
Thanks again.
Sep 21 '06 #4

P: 10
My apologies I was careless

SELECT Left(I.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS,
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8, "0000.00") AS Days,
T.ENROLLMENTSTATUSDESCR
No problem and thank you!!!!! It worked and I appreciate it greatly. This forum has been so helpful. As a novice that is forced to learn by trial and error, it is really great to have people like you that are willing to help others. Thanks again.
Sep 21 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.