Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Location
    Canada
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Type Problem (Access 2000)

    Edited by HansV to remove superfluous line ends.

    Hello there,

    I've got a function RoundIt as a substitute for Access' Round function. RoundIt accepts a number (possibly Null) and a specified number of decimal places and returns either Null or a double-precision rounded number.

    Public Function RoundIt(ByVal varValue As Variant, intNumDigits As Integer) As Variant
    'This will round things like earthlings do, in stark contrast with the Round() function
    Dim varMultiplier As Variant

    If Not IsNumeric(varValue) Then
    RoundIt = varValue
    Exit Function
    End If

    varMultiplier = CDec(10 ^ intNumDigits)
    RoundIt = CDbl(Int(varValue * varMultiplier + 0.5 * Sgn(varValue)) / varMultiplier)
    End Function

    As you can see, if the value passed to RoundIt is Null, RoundIt returns a Null. Otherwise RoundIt rounds the number and then coerces the result to double-precision. If I call RoundIt from code, it works properly. Checking the result with TypeName tells me that RoundIt returns either Null or Double. My problem is when I call RoundIt from a query. In that case RoundIt returns either a Null or a String. The fact that it returns String variables instead of Double is causing me all kinds of problems because Access then doesn't treat the result as a number for formatting, etc. Due to the nature of the application, it is essential that RoundIt be able to accept and return Nulls. If not, I could just change the "As Variant" to "As Double" at the end of the first line and I'd be done.

    Can anyone help me out? Thanks in advance!

    James

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Type Problem (Access 2000)

    It isn't the function's fault, it's the way query engine evaluates the results of a function. If you test the function using the vartype of the result, you'll find that it is a double when you return a double number and a Null when the value is null. The query engine is looking at the expression and deciding that since it can be null, it can't be interpreted as a number.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Location
    Canada
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Type Problem (Access 2000)

    Hi Charlotte,

    Thanks for you response. Do you know if there's any way to get Access to preserve the proper data type in the query. It seems to me that if a function explicitly returns a double-precision value the result ought to be saved in the query as double-precision. I'm sure the Access development team had their reasons for doing this differently, but are you aware of any other way to get it to work the way I was hoping it would?

    One solution I have come up with is to feed the results into yet another query that has all the same fields but uses Iif on the ones I'm concerned about. For instance, if one of the fields that I want to be either Null or double-precision is named Weight, then the entry in the second query is: IIf(Isnull(Weight),Null,CDbl(Weight)). This yields the results I want: the field is then either double-precision or Null. But it's cumbersome and slow. Any better ideas?

    Thanks again!

    James

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Type Problem (Access 2000)

    The function only returns a double if the field isn't null. You could modify your function to return a zero instead of a null when a null was passed in, and that would be handled consistently as a number in the query. Rounding a null is impossible anyhow, so passing a null into the function is pointless. The alternative would be to use an IIf in your query like this:

    <pre>IIf(IsNull([fieldname]),[fieldname],Roundit([fieldname],2))</pre>


    ... where fieldname represents the name of the field you're passing into the function. I don't see the point of a second query.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2001
    Location
    Canada
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Type Problem (Access 2000)

    Hi again,

    That's a very good idea. I still wish Access would preserve the data type that is returned by the function, thus eliminating the need for any of this. But ours is not to reason why. Just so you know, the app distinguishes between Nulls and zeroes, so I can't have RoundIt return a 0 when a Null is passed to it. Anyway, I'm definitely going to use your recommendation. It's faster and simpler than any of the ideas I came up with. Thanks!

    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •