Nested IIF(nz.. in query - syntax - argh! 4

[sumoalex]

I need to modify the following:

IIF(nz([Date1],0)=0,[Date2],[Date1]

to say: if Date1 is null, use Date2 but if Date2 is null use Date3 but if Date3 is null, use Date4.

Is there a nested IIF(nz syntax guru out there?

 

[PHV]

BTW, here my version:

'A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function MyMin(ParamArray Args())
Dim i As Long, rv
If UBound(Args) >= 0 Then rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If IsNull(rv) Or rv > Args(i) Then rv = Args(i)
Next
MyMin = rv
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

quote PHV] : ... just signaled you that your function was buggy ... " [/quote]

Sorry, Old, slow and dense. I didn't read where you mentioned a bug. Still can't find it. Seemed to work fine for me. Still at a loss.

"I'm not sure that your code ... is appropriate ... "

Probably just as well that you're satisified with whatever you're doing. NO sense getting confused at this point?



MichaelRed

 

[PHV]

I asked you if the actual output of your function was what you expected:
? basMinVal(Null, 1, 5, Null, 9, 3, Null, 13.663)

 

[MichaelRed]

quote MichaelRed]

    '[b]Sample Usage:[/b]
    '? basMinVal(1, 5, 9, 3, 13.663)
    '1

    [b][u][i]'?  basMinVal(1, 5, Null, 9, 3, Null, 13.663)
    '[COLOR=red]1[/color][/i][/u][/b]

[/quote]

Again/still, I do not sse a bug in my code. I distinctly did see the post where you:

" ... just signaled you that your function was buggy ... "

I still do not find or see any specific error mentioned, nor do I see an example showing the effect of the mysterious and alleged bug and I continue to miss your specific point. What is the error of my ways?



MichaelRed

 

[flyover789]

I assume, that PHV refers to the fact, that basMinVal(Null, 1, 5, Null, 9, 3, Null, 13.663) returns Null instead of 1

Greetings,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[flyover789]

I should have said, that basMinVal(Null, 1, 5, Null, 9, 3, Null, 13.663) does not return 1, but an empty string or so (how do you call that?)



[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[jsteph]

Between PHV and MichaeRed, we could have a dispute over the true meaning of 'NULL'.

Phv seems correct in pointing out that his example
? basMinVal(Null, 1, 5, Null, 9, 3, Null, 13.663)
should return 1.

However, if Null's are passed then, from a strict programming definition, any Null is 'not known or defined', so any of the Null values could be -1000, or an of them could be 67,000,000--we don't know so we can't say that the "1" value is the minimum. So to return Null is the same as what you get when you add 1+1+1+Null. The answer here is not 3, but Null. On the other hand, the vertical sql Sum() correctly ignores Nulls, so a Sum() in an aggregate query will correcly sum 4 records who's field values are 1,1,1,null, and return 3.

So, the bug is in the documentation--if it were to say that the function should return the minimum non-null argument, then phv's example should return 1. Notice I say 'argument', since 'value'--the term used in Michael Red's doc, could mean that Null is not a 'value'.
--Jim

 

[jsteph]

D'OH!!
I must correct myself in my previous post on two points:

1. The function actually returned Empty instead of Null in PHV's example.

2. Technically, it should return NULL if any Null is in the arg list. But if it's documented that nulls are ignored, then in PHV's example, it should return 1 regardless if the Null is in position 1 or wherever. The function has inconsistency because it returned Empty if the first arg was Null, but not if null was in a further arg.

Sorry for muddying up the waters...
--Jim

 

[MichaelRed]

Point taken. To resolve the issue?:

Public Function basMinVal(varValTyp As String, ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MINIMUM or a series of values _
     excluding NULL - - -  added 5/6/05

    'Sample Usage:
    '? basMinVal("sgl", 1, 5, 9, 3, 13.663)
    '1

    '?  basMinVal("sgl", 1, 5, Null, 9, 3, Null, 13.663)
    '1

    '?basMinVal("int", 9, 1, 5, 3, 13)
    '1

    Dim Idx As Integer
    Dim MyMin As Variant
    Dim varBigVal As Variant

    Select Case LCase(varValTyp)

        Case Is = "byt"
            varBigVal = 255

        Case Is = "int"
            varBigVal = 32767

        Case Is = "lng"
            varBigVal = 2147483647

        Case Is = "sgl"
            varBigVal = 3.4E+38

        Case Is = "dbl"
            varBigVal = 1.797E+308

        Case Is = "txt"
            varBigVal = String(255, "z")

    End Select

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMin = Nz(varMyVals(0), varBigVal)
    End If

    For Idx = 0 To UBound(varMyVals())
        If (Not IsNull(varMyVals(Idx))) Then
            If (varMyVals(Idx) < MyMin) Then
                MyMin = varMyVals(Idx)
            End If
        End If
    Next Idx

    basMinVal = MyMin

End Function

MichaelRed

 

[ESquared]

I know this is the Access VBA forum. But in case anyone just happened to want to know, for what it's worth, in SQL Server there is a built-in function which does what you're asking for. It's Coalesce(), accepts any number of parameters, and it returns the first non-null value in the list or null if all are null. (Although, in testing, when giving it values instead of column names it doesn't like all nulls.)

A VB function to do the same might look something like:

Public Function Coalesce(ParamArray Vals())
   Dim n As Long
   Coalesce = Null
   For n = LBound(Vals) To UBound(Vals)
      If Not IsNull(Vals(n)) Then
         Coalesce = Vals(n)
         Exit Function
      End If
   Next
End Function

That should help the original poster with his actual question. I name it coalesce because then it at least would already mean something to some segment of the database population in the world.