Correct number formatting

[MattGaff]

I have created a function in the VBA code so that when a string is passed, it will return a number:

Public Function FN_Test(ByVal LT1 As String) As Single

Dim rVal As Single

Select Case LT1
    Case "W": rVal = "0.78"
    Case "1": rVal = "1.66"
    Case "L": rVal = "1.66"
    Case "X": rVal = "1.66"
    Case "2C": rVal = "2.17"
    Case "2B": rVal = "2.50"
    Case "2": rVal = "2.50"
    Case "2A": rVal = "2.83"
    Case "3": rVal = "3.32"
    Case "4": rVal = "4.00"
    Case Else:  rVal = Null
End Select

FN_Test = rVal

Right the problems I am facing... I have to put the rVal numbers into quotes "" because for numbers like 2.50 VBA turns them into 2.5. The number 4.00 gets turned into 4# (very annoying).

I want the number to be returned to my query as a 2 decimal place number - even if it has any 0s after the DP. When I use the FormatNumber(FN _Test('4'), 2) in my query, this bizzarely gets turned into a String so that when I want to add the value to another value, they get concatenated as strings.

If anybody has any idea what I am trying to do and can help, please let me know. I will answer as many questions as I can if you think it will help. Would also appreciate alternative solutions!

Matthew

[DonRayner]

You could always declare your function as currency rather than single

Public Function FN_Test(ByVal LT1 As String) As Currency

[FishVal]

Hello, Matthew.

... I have to put the rVal numbers into quotes "" because for numbers like 2.50 VBA turns them into 2.5. The number 4.00 gets turned into 4# (very annoying).

Did it really work for you ???

I want the number to be returned to my query as a 2 decimal place number - even if it has any 0s after the DP.

Number data types don't hold format. Variable having value 2.5 and variable having value 2.50 are exactly the same.

When I use the FormatNumber(FN _Test('4'), 2) in my query, this bizzarely gets turned into a String so that when I want to add the value to another value, they get concatenated as strings.

I would make a guess that it is your own code which converts number to string e.g. with Format() function.

If anybody has any idea what I am trying to do and can help, please let me know. I will answer as many questions as I can if you think it will help. Would also appreciate alternative solutions!

Matthew

Number format is an issue of control displaying that number so tune it appropriately. Or convert final result to formatted string.

Kind regards,
Fish.

[Stewart Ross]

Don's suggestion is an excellent way to achieve what you want to do.

Your current function returns a Single-precision value which should be plenty for what you want to do. You are, however, overly complicating your approach by worrying at the wrong stage about presentation of the result to two decimal places. This is a matter for how the data is presented - it does not change in any way how the value is stored (2.50 is not stored as 2.50 at all - it is stored as a floating-point value which can be converted later to fixed-decimal format for presentation purposes).

For most numeric values you do not need to do anything about the formatting side of things until you present values to users. As most such values are presented in a form or report you simply apply fixed-decimal formatting within the textbox of the form or report concerned.

This approach maintains the highest number of significant digits within any calculations in the processing chain, and therefore the highest possible accuracy of the results shown to users (given that there are always low-level rounding errors involved in floating-point calculations).

-Stewart

ps Apologies Fish for cross-posting - yours was not there when I started this one!

[NeoPa]

Pretty well everything that needed saying has been said, and well said, already.

I would just bring your attention to line #16 though. If a function is designed to return a Single (or pretty well any specific data type), as opposed to a Variant, then assigning a Null value will cause the program to crash. This is not a valid return value. If this is important, then the function needs to be declared as Variant.

Please pay most attention to Fish's and Stewart's posts though. The most important points are explained there for you.

[OldBirdman]

According to Access 2000 Help,

FormatNumber Function

Description

Returns an expression formatted as a number.

But no function returns an expression, This function evaluates an expression and returns a string. This string may include digits, the prefix operator "-", parentheses, group delimeters, and decimal points.

Attempts to force the internal representation of the number to store zeros is not going to work. The Currency Data Type is not going to change this. 5 1/2 = five and-a-half = 101.1 in binary. The fractional part of a decimal number may or may not be able to be represented exactly when converted to binary. The Currency Data Type allocates enough places to store the fraction so that accuracy is probably not affected during calculations. For the values presented in the original post, using Currency will not change anything.

I suggest that you keep everything as 'Single'. Remove the quotes in your function, remove the FormatNumber from the queries, and get the values. Do the formatting to display the final results in the control's Format Property, or as the final result in a query.