[DateRequired] [TimeRequired] Compared to Actual Time to change background properties

[Larry G]

It seems the forums have changed since the last time I logged in or posted. So, I hope I’m in the right category, if not I apologize and would appreciate a reply advising the correct category for the issue.



I have been working in Access 2000; that runs over a network on XP and Win 7 PCs. I’m not exactly a novice but when it comes to VBA and more complex issues I’m sure I fall into the Newbie group.

My current challenge is to create a dbase that will, through the use of a continuous form, illustrate a list of the work or tasks required to be performed by our warehouse crew in a given day sorted (ascending) by the specific time that the tasks are required to be completed.

The first two fields on the data entry form are: [DateRequired] [TimeRequired] followed by several other fields (mostly text) that provide more information about the given task.

[DateRequired] = a Date/Time field formatted in the backend as a Short Date – User enters the DATE Required as 10/3/12
[TimeRequired] = a Date/Time field formatted in the backend as a Short Time – User would enter the specific time the task is expected to be completed – ie: 10:00am

I would like for the background properties of all the fields in a given row in the continuous form to change color when the [RequiredTime] is 60 minutes or less from the actual time. For example change all the fields in a given row change to red when there is 60 minutes or less time left to complete the task in that row.

I’m not totally sure, but I thinking I can’t use the date/time fields as I currently have them structured and certainly not sure how to set them up so that they compare the data entered with the current time to change background properties.

After searching the net, I thought it best to seek assistance where I know from past experience, is the BEST place to find it.

Thanks in advance for any assistance, guidance or suggestions anyone might offer. I’m hoping that this is possible. If it is should a suggestion include code, please be kind enough to include all the key stokes and precisely where they need to placed.

Much appreciated….Larry G.

[pbaldy]

You can use Conditional Formatting for this. If the presentation is such you can just compare the time field to the current time. If the date needs to be included, the expression would be:

([DateRequired]+[TimeRequired])<DateAdd("n",60,Now())

if I understand correctly that anything within 60 minutes of now is highlighted.

[Larry G]

THANKS very much for the reply/assit.

I will try your suggestion, but I think I also need additional key strokes to change the property/backround color.

I think an "If then" or "Iff then" statement someplace that changes the background color, maybe in the forms format event?

If you agree maybe you can assit with the key strokes and best place of same?

Thanks again...regards...Larry G

[pbaldy]

You missed the mention of Conditional Formatting. In 2000 it was in Format/Conditional Formatting. It's the simplest way to get record by record formatting in a report, and the only simple way on a form.

[Larry G]

Sorry...seems I'm still not understanding your suggestion.

I have tried "conditional formatting" (under format menu) for [timerequired] but I'm not getting the xpected results. For Example: in condtion 1...I have used "field value is" then "equal to" (I've also tried other selections) then in the 3rd field I pasted the text you sent..." ([DateRequired]+[TimeRequired])<DateAdd("n",60,Now())" and then selected the backcolor...saved...and opened the form...but..the background color did not change (for the combinations I tried).

I must be doing something wrong..please try to get me on the right track. Thanks for your patients.

[pbaldy]

Try choosing Expression Is in the first dropdown.

[Larry G]

BIG THANKS to you and all the other folks that provides invaluable assistance.

I guess I should have realized that I needed to choose Expression...dah!!...I added it to each field in the record and it works great to change the back color to red when they are 60 min or less from current time.

I set the fields in the row with the conditions previously discussed...to change to red back color....just wondering/curious...if I add a second condition lets say...([DateRequired]+[TimeRequired])<DateAdd("n",90,Now()) ---90 mintues instead of 60 -- and then set the back color to orange...should that work???

Once again...THANKS for the previous assist...regards..Larry G

[pbaldy]

Happy to help. Yes, you should be able to add the second condition with that expression. In versions prior to 2010, you could have 3 conditions.