Help shortening code for Query Criteria

[Ekhart]

I am trying to write a bit of code for a field in a query but am running in to length contraints. I did what I could to shorten the field names and the only other thing I can think of now to help is to shrink the text displayed by the code for each instance, but I would really rather avoid that if I can. [Alert] is an unbound field and not in the table. The problem is I am at my character limit for a field entry. Any suggestions on how to either shrink this one, or perhaps have 2 hidden fields that each make up half of the total script and then a third field that displays what is in either Alert or Alert2 fields? (There is more to the code, this is just what will fit currently)

Alert: IIf(([LA]="First Call" , "Second Call" Or [LA]="Letter Sent") And [AD] Is Not Null,"Apt Date, Check Last Action",IIf(([LA]="Packet Faxed" And [DA]<Date()-2 And [U]="Stat") Or ([LA]="Packet Faxed" And [DA]<Date()-10 And [U]="Routine"),"Check for Apt",IIf(([LA]="Uploaded to Choice" And [DA]<Date()-2 And [U]="Stat") Or ([LA]="Uploaded" And [DA]<Date()-10 And [U]="Routine"),"Check for Apt",IIf(([LA]="First Call" And [DA]<Date()) Or ([LA]="Second Call" And [DA]<Date()) Or ([LA]="Letter Sent" And [DA]<Date()-13),"Attempt to Contact",IIf([LA]="Scheduled" And [AD] Is Null,"No Apt Date",IIf(([LA]="Scheduled" And [AD]<Date() And [U]="Stat") Or ([LA]="Scheduled" And [U]="Routine" And [AD]<Date()-6),"Apt Date Passed",IIf(([LA]="Records Requested" And [DA]<Date()-6) Or ([LA]="Records Received" And [DA]<Date()-6),"Check Records",IIf([LA]="Awaiting Approval" And [DA]<Date(),"Check Approval",IIf(([LA]="Other" And [OD]<Date()) Or ([LA]="Other" And [OD] Is Null),"Other Date")))))))))

[Micron]

I used to do this sort of thing (but never to this level) and kind of wore the success as a badge of achievement. I don't see it that way these days.
My suggestion would be to convert all of this to a function and return it to your query field: Alert:myFunction(). If need be, you can pass parameters that relate to a query field.

[davegri]

Try something like this. The function will calculate the value that you need in the query.

Private function fcnAlert()
fcnAlert = Alert: IIf(([LA]="First Call" , "Second Call" Or [LA]="Letter Sent") And [AD] Is Not Null,"Apt Date, Check Last Action",IIf(([LA]="Packet Faxed" And [DA]<Date()-2 And [U]="Stat") Or ([LA]="Packet Faxed" And [DA]<Date()-10 And [U]="Routine"),"Check for Apt",IIf(([LA]="Uploaded to Choice" And [DA]<Date()-2 And [U]="Stat") Or ([LA]="Uploaded" And [DA]<Date()-10 And [U]="Routine"),"Check for Apt",IIf(([LA]="First Call" And [DA]<Date()) Or ([LA]="Second Call" And [DA]<Date()) Or ([LA]="Letter Sent" And [DA]<Date()-13),"Attempt to Contact",IIf([LA]="Scheduled" And [AD] Is Null,"No Apt Date",IIf(([LA]="Scheduled" And [AD]<Date() And [U]="Stat") Or ([LA]="Scheduled" And [U]="Routine" And [AD]<Date()-6),"Apt Date Passed",IIf(([LA]="Records Requested" And [DA]<Date()-6) Or ([LA]="Records Received" And [DA]<Date()-6),"Check Records",IIf([LA]="Awaiting Approval" And [DA]<Date(),"Check Approval",IIf(([LA]="Other" And [OD]<Date()) Or ([LA]="Other" And [OD] Is Null),"Other Date")))))))))
End Function

Then in your query:

Alert: fcnAlert()