I have the following two cases in my sql query I would like to round the results (round 0) however I cannot figure out the correct syntax to use round in this instance.. any help on this would be greatly appreciated - CASE WHEN tbl_CCMonitorSheetAnswer.Answer = 'False' THEN (tbl_CCMonitorFormQuestion.Points - tbl_CCMonitorFormQuestion.Points)
-
WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE tbl_CCMonitorFormQuestion.Points END AS [Achieved Points],
-
-
CASE WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE tbl_CCMonitorFormQuestion.Points END AS [Max Points],
5  2579  ck9663 2,878
Recognized Expert Specialist
Round(0) ?
Do you want to remove the decimal place?
Here's the full ROUND syntax.
-- CK
Yes that is correct (I want to remove the decimal place).. and i have read over that but I cannot figure out how to use round in conjunction with the case syntax (my example above) how would i incorporate round into that select?
I can do it on the reporting end and use Round(Fields!Ac hieved_Points.V alue) but it then rounds all Null Values to 0 and null values need to remain Null
Also if i modify my query like the following.. it only rounds to the nearest whole number but leaves the decimal place - CASE WHEN tbl_CCMonitorSheetAnswer.Answer = 'False' THEN Round(tbl_CCMonitorFormQuestion.Points - tbl_CCMonitorFormQuestion.Points, 0)
-
WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE Round(tbl_CCMonitorFormQuestion.Points, 0) END AS [Achieved Points],
-
CASE WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE round(tbl_CCMonitorFormQuestion.Points, 0) END AS [Max Points],
ck9663 2,878
Recognized Expert Specialist
I can do it on the reporting end and use Round(Fields!Ac hieved_Points.V alue) but it then rounds all Null Values to 0 and null values need to remain Null
Then a CASE function would do - Value = case when Fields!Achieved_Points.Value is null then null else Round(Fields!Achieved_Points.Value) end
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: steve |
last post by:
Hi,
I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:
update gbkmut
set bdr_hfl = round(bdr_hfl,2)
can anyone help me?
|
by: Peter Scheurer |
last post by:
Hi,
we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.
select 6.56 - round(convert(float, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419
|
by: dkintheuk |
last post by:
Hi all,
I have a form with some unbound combo's and a list box that are used to
requery the dataset behind the form.
All was working fine until i added the list box.
I have the record set of the form looking at that value sin the combo
boxes and handling everything loverly but when i try to use the data
from the list box, it all goes a bit wierd...
|
by: Jeff |
last post by:
using access db and asp
i have a variable from a form
sRound = request.form("round") and for now let us say this value is 3
now i want to include that 3 in sql, but it is part of a field name which
is round3_report
cm.CommandText ="UPDATE rounds SET round" & sRound & "_report = '" & var5 &
"' WHERE Name ='" & sName & "' AND meeting = " & var4 & ""
|
by: roz |
last post by:
Hello,
I'm fairly new to VB programming and I've been going round the bend
with this problem, so any nudges or shoves in the right direction would
be greatly appreciated.
I have a template form in Word. The information that I need to populate
this form is from 2 tables in an Access 2002 database (patient details
and GP details). Once the user has entered the patient and GP etc
details, they need to produce a case summary (including...
| | |
by: Bob Alston |
last post by:
I have recently been trying to determine the best technique to pull the
least amount of info across the LAN link in a slow speed LAN situation
(e.g. < 10 Mbps), where data volume = performance.
After much confusion from reading a lot of info, some of which had
conflicting info, it appears that setting the record source of a form
equal to a sql statement that has the selection criteria as part of the
sql statement, rather than using a...
|
by: Henrootje |
last post by:
I am looking for a way to round down the results of a calculation in a
query
f.e.:
in a query this calculation is performed:
a/b = c
5/3 = 1,666666667
|
by: tangoal |
last post by:
I have a table1 as:
id(int); percent(double,2); a(double,2); b(double,2); c(double,2);.....z(double,2)
1; 0.12; 1.33; 1.26; 1.89;........
2; 0.11; 1.55; 1.22; 1.56;........
:
:
and many rows
i always need to sum up query as:
SELECT SUM(ROUND(a*percent,2)+ROUND(b*percent,2)+ROUND(c*percent,2)) AS sum_abc, SUM(ROUND(d*percent,2)+ROUND(e*percent,2)+ROUND(f*percent,2)) AS sum_def ...... FROM table1;
|
by: deanoooo812 |
last post by:
I have an Access query (written in MS Access 2000 - thats all we've got - don't get me started on that topic...) for making pharmacy dispensing labels based on an extract from an automated dispensing machine extract.
In this query, I use an expression to calculate the total tablet requirement from a combination of the table value of Sum of DailyQty * DaysSupply.
DaysSupply is a parameter that is entered by the user when running the query....
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
