Data type interpretation problem

[popoyan78]

I'm trying to return a number from the following function. The number is defined as double, and I tried declaring all my variables as double prior to variant. If i substitute focusnumber with a specific example of a number I'm trying to search (5.002 for example) I receive the desired result. Otherwise varx always =0. Please help.

Function roottransaction(FormulaItemNumber As Variant, FocusNumber As Variant) As Integer
Dim varx As Variant, vary As Variant, x As Integer


varx = FormulaItemNumber
x = 0
Do
    vary = DLookup("[Previousid]", "FormulaItemsDescriptionView", "[FormulaItemNumber] =" & varx)
    If vary = 0 Then
        varx = 0
        x = 1
    ElseIf (vary = FocusNumber) Then
        varx = 1
        x = 1
    Else
        varx = vary
    End If
Loop Until x = 1

roottransaction = varx
End Function

[NeoPa]

I'm afraid the explanation seems as much of a mess as the code.

This code appears to be half way through an experimental stage. That's not very well appreciated. It puts the extra work of trying to work out what's going on down to the experts and that's not, nor should ever be, our responsibility.

You have various variables defined. Some are variant and some integer, yet you claim that the function should be returning a double.

Public Function RootTransaction(FormulaItemNumber As Double, _
                                FocusNumber As Double) As Integer
  Dim dblX As Double, dblY As Double
  Dim blnLoop As Boolean

  dblX = FormulaItemNumber
  blnLoop = True
  Do While blnLoop
      dblY = DLookup("[Previousid]", _
                     "[FormulaItemsDescriptionView]", _
                     "[FormulaItemNumber]=" & dblX)
      If dblY = 0 Then
          dblX = 0
          blnLoop = False
      ElseIf (dblY = FocusNumber) Then
          dblX = 1
          blnLoop = False
      Else
          dblX = dblY
      End If
  Loop
 
  RootTransaction = dblX
End Function

Try this out and see what happens.

If it fails then we need a clear and precise explanation of what goes wrong. Quote line numbers where necessary.

In future, please put your question together with a little more care before posting.

[ADezii]

The Return Value of your Function is set to Integer meaning you will 'never', 'ever' retrieve a Double from this Function. The Function Return Value, assuming it would be a Double, would be coerced to an Integer. A simple case will illustrate my point:

1. Function Definition with Integer Return Value:
   

   Public Function fTest(intOne As Integer, intTwo As Integer) As Integer
     fTest = (intOne / intTwo)
   End Function

   Debug.Print fTest(1, 3) yields 0
2. Function Definition with Double Return Value:
   

   Public Function fTest(intOne As Integer, intTwo As Integer) As Double
     fTest = (intOne / intTwo)
   End Function

   Debug.Print fTest(1, 3) yields 0.3333333333333 33
3. Try:
   

   Public Function RootTransaction(FormulaItemNumber As Double, _
                                   FocusNumber As Double) As Double
   ...

4. Any questions feel free to ask.

[Stewart Ross]

Hi. To add to NeoPa and Adezii's comments, the choice of name for your function does not help with self-documenting it (what does 'roottransactio n' mean to anyone reading it)? And there are no comments in your code to explain what it is trying to do.

In line with NeoPa's comments I did try to read your code but could not work out what it was doing.

On the use of numbers Adezii has pointed out that you are returning an integer value from your function, so it can never return a double. I would also point out two things: (1) using variants for numeric variables is a mistake, unless you really want and need Access to determine what the precision of a value should be (guessing on your behalf every time), and (2) you are using comparisons for equality on values which are (or are supposed to be) floating point (i.e. single or double variables). Floating point values are always subject to small representationa l errors (of the order of 10^-11 to 10^-14) which will cause comparisons for equality to fail, even when the values are the same to 10 decimal places. In these circumstances you need to define a 'near-equality' threshold to which you compare the absolute value of the difference if you wish to get floating point comparisons to work reliably. The threshold chosen depends on your application's requirements and the precision of the data (as well as the variables representing that data).

Example:

Const NearZeroValue = 0.000000001
If abs (val1 - val2) < NearZeroValue then ...

-Stewart