Error on query calculation

[heckstein]

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 .ENROLLMENTSTAT US)/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.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, I.UNITS*Count(T .ENROLLMENTSTAT US)/8 AS DAYS, T.ENROLLMENTSTA TUSDESCR
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE (((T.LEARNINGAC TIVITYID)=[i].[LEARNINGACTIVIT YID]) AND ((T.INSTANCECOD E)=[i].[code]) AND ((I.LEARNINGACT IVITYCODE) Like 'ODL%') AND ((I.STARTDATE) Between #9/1/2006# And #9/30/2006#) AND ((I.STATUS)='A' ) AND ((T.ENROLLMENTS TATUS)='C') AND ((T.ENROLLMENTS TATUSDESCR)='Co mpleted'))
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, T.ENROLLMENTSTA TUSDESCR, T.ENROLLMENTSTA TUS

UNION ALL SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, I.UNITS*Count(T .ENROLLMENTSTAT US)/8 AS DAYS, T.ENROLLMENTSTA TUSDESCR
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE (((T.LEARNINGAC TIVITYID)=[i].[LEARNINGACTIVIT YID]) AND ((T.INSTANCECOD E)=[i].[code]) AND ((I.LEARNINGACT IVITYCODE) Like 'ODT%') AND ((I.STARTDATE) Between #9/1/2006# And #9/30/2006#) AND ((I.STATUS)='A' ) AND ((T.ENROLLMENTS TATUS)='C') AND ((T.ENROLLMENTS TATUSDESCR)='Co mpleted'))
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, T.ENROLLMENTSTA TUSDESCR, T.ENROLLMENTSTA TUS
ORDER BY I.LEARNINGACTIV ITYCODE, I.CODE, I.LEARNINGACTIV ITYTITLE, I.STARTDATE DESC;

Any help or suggestions would be greatly appreciated. Thank you.

[MMcCarthy]

Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8 AS DAYS, "0000.00")

[heckstein]

Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8 AS DAYS, "0000.00")

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

SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8 AS DAYS, "0000.00"),
T.ENROLLMENTSTA TUSDESCR

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.

[MMcCarthy]

My apologies I was careless

SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS,
Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8, "0000.00") AS Days,
T.ENROLLMENTSTA TUSDESCR

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

SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8 AS DAYS, "0000.00"),
T.ENROLLMENTSTA TUSDESCR

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.

[heckstein]

My apologies I was careless

SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS,
Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8, "0000.00") AS Days,
T.ENROLLMENTSTA TUSDESCR

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.