June 4, 200322 yr I have a calculation I can not work out. I am designing a expenses and income database for a small non profit organisation. I have - Due Date: this is the date the invoice is due for payment. - Date Paid: Date the invoice is paid. - Amount Paid: Amount paid on the invoice. - Paid: This field shows "PAID" once the Date paid field is Valid, else "NOT PAID" - Status - Calculation that determines if the invoice for payment is "Over Due", "Due Today" or "Not Due" (I had help form this forum with this cal .... many thanks :-) ): Case(Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due") Here's the catch: I would like the status field to be blank (or maybe something else such as "PAID" displayed) if the "Date Paid" field is Valid. In other words if the invoice is paid you no longer need the "Over Due", "Due Today" or "Not Due" in the Status field. I hope I have explained this clear enough. I just need an idea where to go, as I would still like to work most of it out so I can better understand. Do I use the current Case statement in Status, or something else? Many thanks Trevors
June 4, 200322 yr Case(Paid="PAID","PAID",Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due") or If(Paid="PAID","", Case(Due Date<Status(CurrentDate), "Overdue", Due Date = Status(CurrentDate), "Due Today", Due Date>Status(CurrentDate), "Not Due")) if you like grouping more.
June 4, 200322 yr Author Hi Christian ..... I tried the Case one before I posted, but it did not work .... The status displays whatever the date is, e.g Over Due, Due Today, Not Due etc. Just tried the IF statement and the same ..... Once it chooses the status display (Date Due etc) it does not update when paid. Trevor
June 4, 200322 yr Author Hi Christian ..... I seem to be defining the problem. The Cal you gave does work but not with the PAID field. The PAID field is a cal from the DATE PAID field: If( IsEmpty(Date Payed),"NOT PAID", "PAID") I creted a TEST field and it your cal works fine, but it does not work with the PAID field, could it have something to do with If( IsEmpty(Date Payed),"NOT PAID", "PAID") :?? many thanks Trevors
June 4, 200322 yr Trevors, Hope this addresses your problem. You could do a lot more with the status calc if you want. Regards, Dean
June 4, 200322 yr Trevors, check the result of your Paid field calc, it's probably set to number instead of text.
June 4, 200322 yr Author Hi Dean .... Not sure what you mean here .... the attachment contained 3 text fields only? Also what did you mean by "You could do a lot more with the status calc if you want", please tell :-). Thank for your help Trevor
June 4, 200322 yr Author Hi Christian .... I have it set to text ..... Mmmmmmm ...... I have done something wrong. Trevor
June 4, 200322 yr Trevor, There is one last question, that may be we should have asked at first.... Are all your fields coming from the same file, or are there some relationships involved here that you didn't tell us about ?
June 4, 200322 yr Author Hi ... All coming from the same file .... all working now thanks to Christian .... many thanks. Trevors
Create an account or sign in to comment