asked on

Changing date field color within query - Access/VBA

Hi Experts

I have following condition for which i would like to have fields highlited with colors when query results being viewed in Datasheet view.

1 = Red
2 = Yellow

IIf(([DATEDUE]<Date()),1,IIf([DATEDUE]>Date() And [DATEDUE]<DateAdd("d",+30,Date()),2))

Thanks

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drivers

ASKER

Hi

Thanks for suggestion, due to nature of this dynamic query its lot of overhead for me. It is actually being exported to excel and was hoping that formatting show up in excel too..

There is a suggestion somwhere to use field Format property within QBE using 0[Black];0[Red];0[Black], however trying to use with logic (two colors as stated above) but can't seems to figure out.

Any ideas?

Thanks

peter57r

The field formats represent positives ; negatives; zero so you can't use the feature to distinguish between positive values.

( Although if it could be used, then the formatting would carry over to Excel on an export.)

I'm afraid I can't see a solution for you using the query datasheet.

drivers

ASKER

well i guess i have to take that as last resort, but before i am wondering is it possible if i can call this from creating a function? if it is then can you give me high level example

some thing like this

if queries!testquery!Datedueflag = 1 then
queries!testquery!Datedueflag.backcolor(or fontcolor) = vbred
elseif ......
endif

Thanks

peter57r

In a continuous form or datasheet , the only way to get different colours in different records is to use conditional formatting.

If you use code, then the colour that is appropriate to the current record is applied to all visible records.

drivers

ASKER

ok..thanks for your help anyway