Divide in Access Query Won't display results less than 1

Hi all,
I have been trying to get an expression to work in an Access query. I thought i was writing my code wrongly until i decided to try a simple sum instead...  

SELECT     2 / 10 AS [Sum]

This equals 0 according to access. If I change it to :

SELECT     2 * 10 AS [Sum]

This equals 20, as you would expect.

Why won't access divide numbers where the total is less than zero??

crichAsked:
Who is Participating?
 
namliam_ehtConnect With a Mentor Commented:
Default for an expresion field is an integer, you need to change it ... to a double?! or single... format


Greetz
0
 
will_scarlet7Commented:
I think it might just be a problem access rounding your sum as 2 devided by 10  will equal .2 which comes to 0 when rounded off.
0
 
Arthur_WoodCommented:
what version of Access are you using, as I just entered

SELECT     2 / 10 AS [Sum]


in Access 97 and 2002, and got 0.2 as the result, so there must be something else that you are doing, but not telling us in your question.  EXACTLY what are you trying, and what does the complete SQL look like?

AW
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Gustav BrockCIOCommented:
Either you have written:

  SELECT 2 \ 10 AS [Sum]

or you have set the Format property of that field to something with zero decimals. That (Format) will, however, only affect the way the number is displayed, not the actual value, 0.2.

/gustav

0
 
Arthur_WoodCommented:
2 \ 10 does, in Access, what is called INTEGER division, which returns the INTEGER value of the division, not the 'real' result


2\ 10 ==> 0

2/ 10 ==> 0.2

AW
0
 
crichAuthor Commented:
I think the problem is because i was using views in views on sql server and maybe views forget datatypes?? I had to use CONVERT(decimal, table.field) to get the right calculations.
0
 
crichAuthor Commented:
Am accepting this as i think it was the closest as it related to datatypes.
0
All Courses

From novice to tech pro — start learning today.