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.
4 1656
Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS, "0000.00")
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.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |