Remove decimal point if no decimal numbers 2

[mbowler9]

Hello all.

I have a query that returns some numbers. The numbers can be integers or numbers with decimals. I want to be able to display up to 2 decimal places if present, but here is my problem. When using "#.##;;0;" in the format property the results are mixed. It works fine if there is a decimal, but if there isn't, I will get an integer followed by a decimal. Thanks in advance.

Result Formatted
14.34567 14.35
9.67 9.67
50 50. (I want to remove this decimal)

 

[PauloRico]

There may be a simple answer to this but here is what I'd do.

Create a global module and add the following code -

Public Function GetMyFormat(dblValue as Double) as Double

Dim lngValue As Long

lngValue = CLng(dblValue)

If (dblValue-lngValue)>0 Then
GetMyFormat=Format(dblValue,"#.##&quot
Else
GetMyFormat=lngValue
End If

End Function

Replace the Format function in your query with the above function call and ensure that the field in the query is formatted as General Number with Auto decimal places.

Hope this helps

 

[CajunCenturion]

After the Format function, you might try the following:

If (Right(FormattedValue, 1) = ".&quot Then
FormattedValue = Left(FormattedValue, Len(FormattedValue) - 1)
End If


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mbowler9]

I haven't actually called a function from the query design veiw before. How would I go about this?

I have a calculation that returns my numbers and the way I tried it was "Expr1: GetMyFormat(the calculations)". I got an error that said "undefined fuction 'GetMyFormat" in expression."

Thanks

 

[PaulBricker]

To call a function from a query, you put the function call on the Field line of your query like this

Expr1:GetMyFormat([FieldNameThatStoresNumbersHere])

You refer to them as calculations so if it's a calculated value you should probably post what expression you use to calculated it.

Paul

 

[CajunCenturion]

Create the function in a Module as declare it as Public.

Public Function GetMyFormat(fldVal As String) As String

GetMyFormat = Format(fldVal, "####.##&quot
If (Right(GetMyFormat, 1) = ".&quot Then
GetMyFormat = Left(GetMyFormat, Len(GetMyFormat) - 1)
End If

End Function

The in your sql statement, reference the function

Select GetMyFormat(ColName) as FormatCol

and use FormatCol as the Control Source

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mbowler9]

Here is the query that I use as seen in design view.

<=3 yrs: ([SERVER AGE ITO 1]![less3]/[SERVER AGE ITO 1]![Tot])*100

I have tried &quot;<=3 yrs: GetMyFormat(([SERVER AGE ITO 1]![less3]/[SERVER AGE ITO 1]![Tot])*100)&quot;

but get the error mentioned above.

Does this help?

Thanks

 

[PaulBricker]

Normally you would do your calculations in one column and then call the function in the next with this type of expression.

Expr1:GetMyFormat([<=3 yrs])

But please DON'T use <= in an alias name. Using mathmatical operators could cause a lot of trouble.

Paul

 

[mbowler9]

Here is the new scenario. I renamed the column &quot;<=3 yrs&quot; to &quot;test&quot; and performed the same calculations. I put a column right next to it as follows:

Test2: GetMyFormat([test])

If I show the first column I get the message &quot;Undefined function 'GetMyFormat' in expression.&quot;

If I hide the first column I get the message &quot;The Microsoft Jet Database engine does not recognize '[test]' as a valid field name or expression.

I have a module named 'GetMyFormat' that has the following code inside:

Option Compare Database
Option Explicit

Public Function GetMyFormat(fldVal As String) As String

GetMyFormat = Format(fldVal, &quot;####.##&quot
If (Right(GetMyFormat, 1) = &quot;.&quot Then
GetMyFormat = Left(GetMyFormat, Len(GetMyFormat) - 1)
End If

Any other suggestions? I am sure I am doing something wrong that is probably really small.

Thanks

 

[PaulBricker]

You can't name a module the same as the function. Name your module modGetMyFormat or something like that and then try it again.

Paul

 

[mbowler9]

That was it.

Thank You.

 

[TheAceMan1]

How are mbowler9 . . . . . . .

It took a bit of manipulation, but I got it for ya!

Deriving the code was easy, the hard part was fighting percision (data types Single & Double) along with rounding & display format (have to allow for the least precise decimal data type). For instance:

In Single Percision, Formatted For Two Decimals
-----------------------------------------------
9.995 displays as 9.99
9.996 displays as 10

This alone revealed that rounding needed to be performed manually!

Be aware, there is no discrete format that will give you resolution. The main reason being that two formats are required, and these two formats are not dependant on positive & negative values, but on wether or not a decimal exist. Access does not have that capability yet. Therefore you are limited to Code In Query!

So, in your query, in a new field, on the field line, copy & paste the following code (Note:YENFN means Your Existing Number Field Name):

FixedVal: IIf(Round([YENFN],2)-Int(Round([YENFN],2))<>0,Format(Int(100*([YENFN]+0.005))/100,&quot;#,###.##&quot,Format(Int(100*([YENFN]+0.005))/100,&quot;#,##0&quot)

Note: this will not work in any Format Property.

And there you have it!


TheAceMan