IIF statement returning #Error

[David Ball]

Hi,

I have an IIf statement in a calculated field in a query and it looks for 4 conditions. It works fine when any of the 4 conditions are met but in cases where none are met, and I want it to return nothing, (" "), it returns #Error.

What is wrong with the formula?

10Jul15: IIf(([StartDate]<=#4/07/2015# And [FinDate]>=#10/07/2015#),[WeeklyQty],IIf(([StartDate]>=#4/07/2015# And [FinDate]<=#10/07/2015#),[Sales],IIf([StartDate] Between #4/07/2015# And #10/07/2015#,([DailyQty]*[FrontDays]),IIf([FinDate] Between #4/07/2015# And #10/07/2015#,([DailyQty]*[BackDays])," "))))

PS, any ideas on making it a lot simpler and shorter would be appreciated to!

Thanks very much

Dave

 

[plog]

When you get this nested, its time for a custom function in a module. No point trying to debug all that crammed logic. You create a function, pass it all the fields it needs to determine/return the result, then you do the logic in that function using as many lines as you need.

With that said the way to debug errors is divide and conquer. Keep reducing the code until you can arrive at the point where it breaks. Take all those individual IIf statements and make them their own calculated fields. Then find the one that breaks and examine the data it is using. Most likely you have a Null value in one of those fields.

But really--time for a custom function.

 

[James Deckert]

You're returning a string value into (I assume) a numeric field. Try NULL instead.

FYI: [StartDate]=#4/07/2015# and [FinDate]=#10/07/2015# falls into the true side of your outside if as well as the true side of your second if. Seems like bad logic.

 

[David Ball]

Thanks guys.

Plog: If I had the slightest idea how to create a custom function I would do so. Thanks for taking the time to reply.

James: Thanks, I'll try Null

Dave

 

[arnelgp]

you have 298 characters in your sql string (using Len()). Ms Access truncate the rest beyond 255 characters.