Conditional formatting help : Max & Where 2

[maxxev]

Hi, I want to use the following in conditional formmating but I can't get it to work. Seems to be very difficult to find guides / advice on conditional formatting in access as google keeps getting up excel pages.

I'm on 2003 BTW.

Here is what I would like to conditinally format:

Max([DateActive]) WHERE ([DateActive])<=Date()

 

[MajP]

This easiest way to do that would be to put a hidden control on the form
For the control source use a dmax or dlookup to return the date. Before hiding it see if it returns the correct date.
Max([DateActive]) WHERE ([DateActive])<=Date()

Call that control "txtBoxMaxDate"

in the conditional formatting set "Field Value" "Equal To" "[txtBoxMaxDate]

 

[maxxev]

Hi, thank you very much for the response howeve I don't know how to do a Dmax or Dlookup, could you give me some pointers please:

Here is what I have:

=DMax([DateActive],[DateActive],([DateActive])<=Date())

 

[PHV]

I don't know how to do a Dmax
You know what ?
The F1 key and google are your friends.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I'm not sure you need DMax() or other. You could try get the maximum date less than Date() with a control source of:

=Max(IIf([DateActive]<=Date(),[DateActive],#1/1/2000#))

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

@dhookom: Thank you very much that appears to have done the job brilliantly.

@PHV: I did look up Dmax and Dlookup that is how I got the format of the code I posted, however I could not work out why the code was not working. Thank you for your response however I honestly did not think of going onto Access help, only Google (which wasn't very useful).

If a mod comes accross this then this is a solved issue.

Cheers.

 

[PHV]

FYI, the correct syntax was:

=DMax("DateActive","NameOfTable","DateActive<=Date()")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886