How to convert generated field in Select Query to VBA? (1 Viewer)

[padlocked17]

All,

I've got the following IIF monster in a field in a select query. Can someone give me some advice on how to make this more manageable? I'm hoping to convert it to VBA as it's just a monster to try and manage or make changes to this. I'd like to convert this to VBA and then reference the result in the select query and not convert the entire select query over to VBA as I'm trying to keep this project as simple to manage for someone else who doesn't know VBA as possible.

Thanks in advance.

GeneratedDueSemi: IIf(IsNull([ARMS_DUE_DT]),IIf(IsNull([ACC_DT]),Null,IIf([Remaining]<1,IIf(DatePart("q",[ACC_DT])<3,CDate("12/31/" & Year([ACC_DT])),CDate("6/30/" & (Year([ACC_DT])+1))),IIf([Remaining]>0,IIf(Year([ACC_DT])<Year(Date()),IIf(DatePart("q",Date())<3,CDate("6/30/" & Year(Date())),CDate("12/31/" & Year(Date()))),IIf(DatePart("q",Date())<3,CDate("6/30/" & Year(Date())),CDate("12/31/" & Year(Date())))),Null))),Null)

 

[Galaxiom]

Write it as a custom function then use the function in the query. This will encapsulate it and make the query look simple to maintain.

Consider the DateSerial function as an alternative to CDate. It is tidier and safe in all regional date formats.