By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Data type interpretation problem

P: 3
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.
Expand|Select|Wrap|Line Numbers
  1. Function roottransaction(FormulaItemNumber As Variant, FocusNumber As Variant) As Integer
  2. Dim varx As Variant, vary As Variant, x As Integer
  5. varx = FormulaItemNumber
  6. x = 0
  7. Do
  8.     vary = DLookup("[Previousid]", "FormulaItemsDescriptionView", "[FormulaItemNumber] =" & varx)
  9.     If vary = 0 Then
  10.         varx = 0
  11.         x = 1
  12.     ElseIf (vary = FocusNumber) Then
  13.         varx = 1
  14.         x = 1
  15.     Else
  16.         varx = vary
  17.     End If
  18. Loop Until x = 1
  20. roottransaction = varx
  21. End Function
Aug 11 '08 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,489
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function RootTransaction(FormulaItemNumber As Double, _
  2.                                 FocusNumber As Double) As Integer
  3.   Dim dblX As Double, dblY As Double
  4.   Dim blnLoop As Boolean
  6.   dblX = FormulaItemNumber
  7.   blnLoop = True
  8.   Do While blnLoop
  9.       dblY = DLookup("[Previousid]", _
  10.                      "[FormulaItemsDescriptionView]", _
  11.                      "[FormulaItemNumber]=" & dblX)
  12.       If dblY = 0 Then
  13.           dblX = 0
  14.           blnLoop = False
  15.       ElseIf (dblY = FocusNumber) Then
  16.           dblX = 1
  17.           blnLoop = False
  18.       Else
  19.           dblX = dblY
  20.       End If
  21.   Loop
  23.   RootTransaction = dblX
  24. 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.
Aug 11 '08 #2

Expert 5K+
P: 8,637
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fTest(intOne As Integer, intTwo As Integer) As Integer
    2.   fTest = (intOne / intTwo)
    3. End Function
    Debug.Print fTest(1, 3) yields 0
  2. Function Definition with Double Return Value:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fTest(intOne As Integer, intTwo As Integer) As Double
    2.   fTest = (intOne / intTwo)
    3. End Function
    Debug.Print fTest(1, 3) yields 0.333333333333333
  3. Try:
    Expand|Select|Wrap|Line Numbers
    1. Public Function RootTransaction(FormulaItemNumber As Double, _
    2.                                 FocusNumber As Double) As Double
    3. ...
  4. Any questions feel free to ask.
Aug 12 '08 #3

Expert Mod 2.5K+
P: 2,545
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 'roottransaction' 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 representational 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).


Expand|Select|Wrap|Line Numbers
  1. Const NearZeroValue = 0.000000001
  2. If abs (val1 - val2) < NearZeroValue then ...
Aug 12 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.