find the correct date

[saintedmunds]

HI
I need a function to find the correct date to do a Dlookup.
My look up need to start on a Wednesday with the time > 8:45:00 the look up part I can do. I'm having trouble working out a way of getting the Correct Start date.

So if the date is a Monday then i need to set my lookup to the wednesday before. if the date is a Wednesday but the time is > 8:45:00 then the lookup needs to be this date not the previous Wednesday.
does this make any sense?

Cheers

 

[Remou]

What happens on Tuesday, Thursday and Friday?

 

[saintedmunds]

Hi

They all go to the previous Wednesday.

Cheers

 

[Remou]

How about:

[tt]sd=Now()
CTime = IIf(Weekday(sd) = 4 And Time > #8:45:00 AM#, sd, sd - 7 - Weekday(sd) + 4)[/tt]