Showing overdue days based upon the data in two other fields

[Detectiveclem]

Hi can anyone help me with the correct formula I need to show the number of days overdue based on data in two other fields.


Field 1 [Date Reqired for Compliance] in which the user inputs the date when someone needs to have responded to a request.

Field 2 [Date Material received] the user inputs the date when items have been received.

Field 3 [Status] in this field I would a formula which will show 'Complete' if Field 2 has a date entered or, if it is blank then display 'Awaits' if the date in Field 1 has not been reached (based on todays date) or show the number of days overdue (like this "23 days overdue").

Thanks any help appreciated

 

[WaterGypsy]

Try: =IF(Date_Recd<>"","Complete",IF(Date_Reqd>TODAY(),"Awaits",TODAY()-Date_Reqd&" days overdue"))

 

[Detectiveclem]

Hi WaterGypsy,

Thank you for coming to my aid again Can I just clarify that the forumla is suitable for ACCESS.

I have tried your formula but Access doesn't like it.

I tried this =IIf([Date MaterialReceived]<>"","Complete",IIf([Date Required ForCompliance>TODAY()-Date Required For Compliance&" daysoverdue"))]

and is


=IF(Date MaterialReceived<>"","Complete",IF(Date Required ForCompliance>TODAY()-Date Required For Compliance&" daysoverdue"))

Niether worked, although the following works, however it doesn't do what I want.

=DateDiff("d",Date(),[Date Required ForCompliance])

Can you assist further?

Thanks Paul

 

[stumac]

You shouldn't save this as a calculated field in a table, in a query or on a form the following should work:

(IIf(Nz([Date Material received])>0,"Complete",IIf([Date Reqired for Compliance]>=Date(),"Awaits",Date()-[Date Reqired for Compliance] & " Days Overdue")))

I have included the spelling mistake 'Reqired' in your field names as wasn't sure if it was deliberate or not.

 

[WaterGypsy]

Sorry ... I assumed Excel and Access would be similar enough in this case ... I really shouldn't make that assumption

I don't have Access here -I'll have a look when I get home

 

[Detectiveclem]

Hi stumac,

Thank you very much, this worked perfectly. Yes thank you also for pointing out my typo, I amended you formula accordingly.

Greatly appreciated.

 

[Detectiveclem]

Hi WaterGypsy,

Thank you for trying to assist, much appreciated. Stumac has resolved my question. But thank you for trying.