473,321 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Error on query calculation

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
4 1656
MMcCarthy
14,534 Expert Mod 8TB
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00")
Sep 21 '06 #2
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
14,534 Expert Mod 8TB
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
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

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

Similar topics

4
by: muser | last post by:
Can anyone run this program through their compiler or if they can see a logical error please point it out. I have my tutor working on it at the moment but I would rather a less ambigious response...
2
by: Steve | last post by:
An Access97 database I wrote contains a dynamic query whose results get exported to Excel. I have WindowsXP and Access97, 2000 and XP installed. When I run the query, it runs trouble free. A...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
0
by: leavandor | last post by:
I am trying to design a query that works with a relationship between a Table and a Query. I am comparing a value in the table with a computed value inside the query. The reason for this is that...
1
by: Andrew Chanter | last post by:
I have an application that produces examination scores. Candidates have a choice of sitting 1 of 2 subjects or both. I have produced a query that gives all the results for candidates that sat...
3
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
5
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new...
4
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The...
3
by: oopsatwork | last post by:
I'm trying to construct an XPath and it is giving me trouble. I have an XML that contains a "Routine". The routine contains "Steps". The steps have different types. Some types of steps have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.