MS Access Question

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.

Can any of you offer any examples or help?

Thanks.

What do you want “flag” to do?

Is it seven days counting weekends or just working days?

Is the receipt date entered with the time?

If it is now 11 AM on Tuesday and the receipt date/time is 3 PM last Tuesday should it be flagged? What if the receipt was 9 AM last Tuesday?

No time involved. Just the date.

Seven days total.

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.

In the form design view, right-click on the date received box and select conditional formatting. Change Condition 1 to “Expression Is” and use


DateDiff("d",[name of receipt date field],Now())>6

as the expression and change the condition 1 fill color to yellow.

Or what K364 said. :smack:

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.

That’s kind of what I figured. I used to be a whiz at this stuff, but if you don’t use it, you lose it. So, I’m slowly re-educating myself.

Thanks for the responses. The users will just have to be happy with the form. It’s going to be me running queries on the data anyway.