Using a color in a query

[amy3000]

I'm working with database I didn't develop so I have some limitations with it. I have a query and a report that give enrollment and exit dates for our clients. I want to be alerted if the exit date that was entered is earlier than the enrollment date. I'd like it to appear in a color in the query and/or the report. Is there a way to enter this into the query criteria or create an expression to identify these cases? Thanks again, Amy

 

[cheerio]

I do not think you can colour an Access query depending on results but I do not have Office XP so that might be a wonderful addition.

You could add another column to your query as a calculated field. Something like this in the column header in design view:

DateComment:IIF([exitdate]<[enroldate],&quot;strange dates&quot;,&quot;&quot

You could also have a similar formula in a text box on a report.

It is possible to embed a query into an Excel worksheet and use conditional formatting to do what you ask and I have done this successfully. It is the solution needing the least skill if you really want color.

You can even embed your Excel worksheet on an Access form if you want the user to stay in Access.

The only other possibility involves using VBA code. As a report runs a series of events happen in turn and it is possible to write VBA code that watches for an event and takes action. The two interesting events for this purpose are Format and Print and you would want these events for the detail section. They occur just before formatting data and just before printing it. The event code could check the two dates and set color as appropriate.

Ken

 

[CTOROCK]

or you could do this on a report on the activate action:

if txtenrol.value < txtenterdate.value then

txtenterdate.forecolor = ??
end if

hope this helps &quot;The greatest risk, is not taking one.&quot;