Is there any way to shorten the length of this calculated field in Access?

[bentley1]

Right now I have a long if statement that states:

Field1: IIf([AV].[Date]>=[Date10] And [Date10] Is Not Null,[Date10],IIf([AV].[Date]>=[Date9] And [Date9] Is Not Null,[Date9],IIf([AV].[Date]>=[Date8] And [Date8] Is Not Null,[Date8],IIf([AV].[Date]>=[Date7] And [Date7] Is Not Null,[Date7],IIf([AV].[Date]>=[Date6] And [Date6] Is Not Null,[Date6],IIf([AV].[Date]>=[Date5] And [Date5] Is Not Null,[Date5],IIf([AV].[Date]>=[Date4] And [Date4] Is Not Null,[Date4],IIf([AV].[Date]>=[Date3] And [Date3] Is Not Null,[Date3],IIf([AV].[Date]>=[Date2] And [Date2] Is Not Null,[Date2],IIf([AV].[Date]>=[Date1] And [Date1] Is Not Null,[Date1],[AV].[Effective_Date]))))))))))

It is getting too long (the "date" fields are actually longer and I can't add date11 without exceeding the limit for text). Is there any way to make this query shorter? I don't think I could do select cases in access but I am not really sure

 

[Joe4]

I don't think I could do select cases in access but I am not really sure

You can, but in a User Defined Function (UDF). This is usually the preferred way of handling long, complex formulas -- create your own function.

See the following links:
Creating and Using User Defined Functions in Access » Bacon Bits
VBA Tips: Build Custom Functions for Your Access Applications