VBA to enter date to field

[Ekhart]

I have a VBA script which autofilters data on a datasheet form. What I would like is for the VBA to use the same criteria it is filtering by to enter a bit of text in to the first field on the form (Alert). An example of the autofilter is:

"[DateofConsult]<" & Format(myval, "\#m/d/yyyy\#")

. This code basically filters any consult that is older than 120 days old. I would like for it to put in the Alert column 'Older than 120 days' so the person running this will know why it hit there. There are many different filters so I want to make it as clear as possible.

I have done this with queries before but due to uncontrollable limitations on my office network, the large union query runs incredibly slow compared to filtering and with union queries I was unable to have an editable query/datasheet form.

[ranman256]

in the query you use behind the form, add an extra field for days: Days: DateDiff("d",[DateofConsult],Date)
then under it as criteria, put >120

save as a filter or use vb to filter the days.

[Ekhart]

This is a good start but since there are many different criteria (not just a date) I am looking at it wont work overall. I really just want a way to use my string and have it use the same info it is using to filter, to add text to the empty 'Alert' field. I can do this with conditional formatting but I would much prefer clear text to a special color to mean something specific has to be done.