Conditional Formatting?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is what I have. I have a report sorted by Week Ending date (Sundays).
Within my report I have a DLookup that looks up values from another query
that is also sorted by Week Ending date. So lets say that the report starts
with Week Ending 1/2/2005, but the DLookup doesn't start having values until
Week Ending 4/3/2005.

Here is what I need. I need that DLookup to shade grey all the weeks prior
to 4/3/2005 that have no values. Conditional formatting works until my
DLookup doesn't have a value or has a zero for a week after then it shades
that week also, and I don't want it to do that. So how can I get a grey box
to only show up before my DLookup has values and not after that on Null or 0
values? VB is what I'm guessing but I can't figure it out by myself. Help
is greatly appreciated. Thanks.
 
It might really help if you would show the DLookup and the Conditional
Format you are using. And, could you clarify "conditional formatting works
until my DLookup doesn't have a value or has a zero for a week after then it
shades that week also" -- I am confused.

Larry Linson
Microsoft Access MVP
 
Here is my control source for my textbox with the DLookup:

=NZ(DLookUp("TOURS2MAIL","CALLS2MAILDMTMP1AM","[WEEK ENDING]=#" & [WEEK
ENDING] & "#"),0)

So every time there is no value for a Week Ending, it fills a zero. I have
conditional formatting to color the background and the text grey when the
value is equal to 0. The problem is that I really don't need it to shade
grey except before the first value shows up. For instance, lets say the
report starts at the beginning of the year, but this DLookup doesn't start
having values until April. So I need it shaded from January to April. It is
doing that, but, say I have a zero in June for the DLookup, then it shades
that week grey also. I need to have it where it just fills grey before the
DLookup pulls numbers, and not after that even if it is a zero. Does that
make sense? The DLookup is a marketing promotion that didn't begin unitl
April, so I just need the report to visually show the grey only before it
began, even though there might be some weeks after it began where it might
have zero production. Thanks for the help, sorry if I'm not expalining
myself well enough.
 
Back
Top