I have been tasked to create a database. Normally, this is not a problem in the least. I have most of it constructed, along with drop down items for the users to use.
However, the boss wants the system to flag or do something when the current date (date user is actually in DB) is seven days past the receipt of action.
I currently have two fields set as date fields. One date is receipt of action in office and the other date field is the date action sent to another office. I don’t have a field for current date (for example, today’s date).
How would I get the system to flag on a record that is seven days past receipt of action in office?
I know there’s a datediff type of command, but I can’t figure out how to get this to do what I need to do.
If possible, (and this is how little I know), I’d like the date received box to turn another color, yellow, for example. If it can’t do that, maybe a beep.
Build a form to display/edit the table(s) if you haven’t already done so.
For the date field that you want to flag, right-click and choose “Conditional Formatting”. The condition should be:
Field Value Is less than Date()-7
Date() returns the y/m/d component of the current system date, but not the time component (ie truncates the time). If this is not what you want use Now() instead.
Choose formatting options so that the field is bolded/red/whatever when the condition is true.
Thanks guys, the datediff command worked great on the DB Form, so that’s a go. However, I can’t get it to work on the table it’s self. I tried building a form to edit the table, even went into the table I already designed in design mode, but when I right click, I can’t get “conditional formatting” to pop up.
It’s okay; I’ll just make sure the end users use the form I created for data entry. Hopefully, they’ll do it.
No, tables aren’t really designed for that sort of thing. You could do a new datasheet autoform and apply the formatting to it, then hide the table. You could also auto-open that form (under tools, startup) when the database is opened.