iif else in query not exactly returning the right results 1

[dpelizzari]

I have never tried this before, but I was asked to create a calculated field based on information in a query. basically, if a field is blank, then select another field, if that field is blank, select a third. Here is what I wrote:

  IIf (LEN( [Remedy Budget])>9 , [Remedy Budget], IIf (LEN ([Lease Budget])>9, [Lease Budget], [VPN Budget]), [VPN Budget])

I think I am missing something in the format, probably an open/close paranthesis

Any help (as always) is greatly appreciated.

 

[r937]

you just had an extra term in there

try this --

IIf( LEN([Remedy Budget]) > 9 
   , [Remedy Budget]
   , IIf( LEN([Lease Budget]) > 9
        , [Lease Budget]
        , [VPN Budget] ))

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dpelizzari]

thanks, r937, a second set of eyes always makes it easier!