CODEWITHSUNDEEP
sqlms-access
user3590101
user3590101

Reputation: 11

get decimal part of number in MS Access

How do you access the decimal part of a number in MS Access? More specifically I want only the component after the decimal point, but not including the decimal point. This must also work for all whole numbers. I've seen this answered for other SQL engines, but they don't work in Access. I can't be much more specific than this because of the sensitive nature of what I'm actually working on.

For example given the following numbers the input is on the left and the output is on the right. Output can be either text or a number.

Source   Correct  Incorrect1  Incorrect2
10.0     0        0.0         .0
3.14159  14159    0.14159     .14159
45.65    65       0.65        .65
173.0    0        0.0         .0
143.15   15       0.15        .15

If I was using C# the following code would give me what I want:

private string getDecimalComponent(double input)
{
    String strInput = input.ToString();
    if (strInput.Contains('.'))
    {
        return strInput.Split('.')[1];
    }
    else
    {
        return "0";
    }
}

Upvotes: 0

Views: 11466

Answers (5)

Melvin
Melvin

Reputation: 1

While this is an old post, there is a split function in VBA. Something like:

ArrayStr=split(cstr(dbl), ".")

Similar to the C# example. If wanting to run sql the Right and Left function could be embedded into the sql as well as cstr. Not sure how well it would run on a large data set.

Upvotes: 0

miroxlav
miroxlav

Reputation: 12194

A stable solution producing String containing decimal places from Double.

SELECT DecimalPlaces(MyColumn) FROM MyTable

where the above user-defined function contains the following code:

Function DecimalPlaces(ByVal value As Double) As String

  Dim intPartLen As String
  intPartLen = Len(CStr(CInt(Abs(value))))

  If Len(CStr(Abs(value))) > intPartLen Then
    DecimalPlaces = Mid(CStr(Abs(value)), intPartLen + 2)
  Else
    DecimalPlaces = "0"
  End If

End Function

It avoids common mistakes, so it is

  • locale-independent - works with any decimal separator (it only assumes it is a single character)

  • preserves precision - avoids subtraction, takes the result only from string representation

Note: those Abs() calls are really required (hint: -0.1)

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8404

Depends on the circumstance. If this is, for instance, in a textbox, you can use InStr to find the decimal, and then use the Mid() function to get the number after it. If it's part of an arithmetic equation, then I would use the Int() function and subtract one number from the other to get the difference.

If you can elaborate on how it's being used, and in what context, I can edit my answer to give you more specifics.

EDIT: After more info came to light, try this:

Public Function GetParts(Temp1 as Double)

Temp2 = Int(Temp1) 
Temp3 = Mid(Temp1, InStr(Temp1, ".") + 1) 

MsgBox Temp2 
MsgBox Temp3 

End Function

Upvotes: 0

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

Something like:

       SELECT 3.14%1 AS mycolumn from mytable

Upvotes: 0

sion_corn
sion_corn

Reputation: 3141

Subtract the integer portion of the value.

Example: 4.25 - Int(4.25) = 0.25

Or, as a sample SQL expression:

SELECT
    [myDecimalNumber],
    [myDecimalNumber] - Int([myDecimalNumber]) as [rightOfDecimal]
FROM tableA

Upvotes: 2

Related Questions