Display Text Based on Field Value

[spyldbrat]

I am VERY BEGINNER user of access, so please try not to use too many technical words with me LOL!

I am trying to create a report. The report is based on our Customer Aging which displays all open invoices (INV #), the balance due on each invoice (BAL DUE) and days each invoice has aged (DAYS AGING). I wondering if I can add text to the top of each Customer Grouping I set up based off the highest number that appears in Days Aging column. I would like set multiple text options based on the numbers. I am not going to provide the exact wording the text because I am pretty sure I can modify the text after I am provided the information on how to do it. But, below is an example.



TEXT EXAMPLES:
Text 1 for 30 to 45 days: You have $xxxx overdue between 30 and 45 days. Please remit.
Text 2 for 46 to 55 days: You have $xxxx overdue between 46 and 55 days. Please remit.
The $xxxxx amount would come the the column called "Bal Due" for the corresponding days.

EXAMPLES:
Customer has 10 invoices all aged between 1 and 27 days: Will not display on report
Customer has 15 invoices all aged between 2 and 42 days: Display Text 1 at the top of the report
Customer has 7 invoices all aged between 3 and 46 days: Display Text 2 at top of the report

I hope you can help!!! Thank you.

[orange]

spyldbrat,
I suggest you post a copy of the database with enough data to illustrate the current issue.

You should be aware that Access does not like/work well with field/object names with embedded spaces or special characters. Yes it will accept them under certain syntax requirements, but they will eventually come back to haunt you.

It will be easier for readers to offer focused responses once they can see you efforts and data in context.

Good luck with your project.

[June7]

More than 1 way to accomplish.

1. Calculate an AgeGrp in query then reference that field in report textbox with expression for conditional Count() and determine which message to display.

2. If gets too complicated to do with expressions in textboxes, build VBA custom function

[Micron]

Maybe a calculated textbox on report (as long as it or the query behind it contains the fields required) like:

="You have $" & [Bal Due] & " overdue between " & Min([Days Aging]) & " and " & Max([Days Aging]) & " days. Please remit."
Since you will be able to modify it accordingly, you'll have to incorporate the correct references to get the values over a group. I presume your query has that covered.

[spyldbrat]

Thank you everyone. I will play with it and see what I come up with.