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?

 

[Ascentient]

Yes, you can do this.

iif(isnull(date1), True Value, iif(isnull(date2),True Value,iif(isnull(date3),True Value, date4)))

Is that what you are looking for?

 

[dhulbert]

Here's a starter,extend this to cover the number of Dates you have.


iif(isnull([Date1]),(iif(isnull([Date2]),[date3])),[date1])

 

[Ascentient]

Actuall I have it backwards.

iif(not isnull(date1), True Value, iif(not isnull(date2),True Value,iif(not isnull(date3),True Value, date4)))

This basically states that if date1 contains a valid value do what is true, otherwise lets test for Date2 (the false condition which tells use date1 is null.)

My first post test if Date1 IS null do something. You do not want this if what I read is correct.

Ascent

 

[Ascentient]

dhulbert's idea will work just as well too, but you will have more ((())) to worry about.

Ascent

 

[sumoalex]

Can I actually substitute the "nz" with "IsNull" (This is in a query)

 

[Ascentient]

Yes, you should be able to. I through this together and tested.


SELECT tblCustomer.[CUSTOMER ID]
FROM tblCustomer
WHERE ((Not (tblCustomer.[CUSTOMER ID])=IsNull([customer id])));

 

[sumoalex]

Thanks a million guys!

 

[MichaelRed]

many suffer from the eye-strain of connting Parens and matching yo the pairs properly. Depending on your specific tolerance level, you may finc that it is generally easier to generate a simply function, which returns t he first non-null or non-zero (or other criteria) value from an array passed to it. You might look for a function "basMinVal" in htese (Tek-Tips) fora for an example which accepts an arbitrary number of arguments and returns the mininum of the set. The Key concept in this and several to many others) is the ParamArray. This acts as the container with an arbitrary number of elements which can easily be determined and evaluated by the function. From there, the evaluation of the individula elements agianst your criteria is usually accomplished in a simple loop. In your case, it would paaear that you could pass the potential values to the function via the Nz function and stop the evaluation at (and return the value of) the first value greater than Zero.




MichaelRed

 

[sumoalex]

I appreciate your response, Michael, but would this work in a query?

 

[MichaelRed]

Of course. Olease use the search capability to find the sample procedure. Review it. I believe aI even left a sample call it it as a comment.

A query would (obviously?) use a "Calculated" field, sending the values as the arg list to the function.

Assuming (always a poor choice) that the calculated field would be "FirstDate" and the Function was named basFirstGT_Zero, your query field would be something like:

FirstDate: basFirstGT_Zero(Nz([Date1], Nz([Date2], Nz([Date3], ... Nz([Date[i]1[/i]])
[end code]

Of course YOU still need to do the homework.



MichaelRed

 

[cmmrfrds]

The Switch function may be easier to read.

SELECT
Switch(adate1 is not null,adate1,
adate2 is not null, adate2,
adate3 is not null, adate3,
adate4 is not null, adate4,
True, "1/1/1900")
as thedate
FROM TableofDates;

'The date 1/1/1900 is used as the default if all 4 are null.

 

[sumoalex]

cmmrfrds:

Cool! Thanks for that.

 

[PHV]

Another way:
Nz(Date1, Nz(Date2, Nz(Date3, Nz(Date4, #1900-01-01#))))

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

 

[sumoalex]

PHV:
Nice tight code. I like that. Thanks!

 

[MichaelRed]

I had thought to bypass this frenzy. Given the aparent profusion of confusion:

Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MINIMUM or a series of values

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

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

    '?basMinVal(9, 1, 5, 3, 13.663)
    '1

    Dim Idx As Integer
    Dim MyMin As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMin = Nz(varMyVals(0))
    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

This code has some minor advantages:

[tab]Like me it is "tried and Tested"
[tab]It permits the use of a variable number of arguments'
[tab]It does not restrict the arguments (in or out) to a specific Type
[tab]It does not require the APriori conversion of Nulls to "other" types (usually zero).

On the other hand, the prohibition aginst returning zeros is left as an exercise for the student. Although such a modification would normally be "trivial", the details may depend on the specifics of the implementation, and the user (calling process) will also need to be equipped to both recognize and respond (APPROPIATLY) to the situation.


MichaelRed

 

[PHV]

Michael, what is the expected output of this ?
? basMinVal(Null, 1, 5, Null, 9, 3, Null, 13.663)

 

[MichaelRed]

Please read the "Docs" (commentary) near the top of the function.




MichaelRed

 

[PHV]

Michael, I just signaled you that your function was buggy ...

 

[sumoalex]

Michael:
I appreciate your responses, but my thread was about nesting "IIF(nz... " in a query, and I've received great responses that I've put into production).

I'm not sure that your code "To return the MINIMUM or a series of values" is appropriate to this thread.