Having Form Open to Specific Date

[mizenerman]

I have a form that has a date field (Clinic Date). In this field users enter a date of a meeting held every Thursday. Records are created in the form that can have a date of next Thursday or a Thursday in December. I would like to have the user open the form and the first record that has a Clinic Date for the upcoming Thursday.

Confusing I know. Here's an example:

1st record - Clinic Date = 06/28/2007
2nd record - Clinic Date = 07/12/2007
3rd record - Clinic Date = 07/26/2007

Today's date = 07/06/2007

Form opens to record with clinic date 07/12/2007.

On the Forms properties I have in the Filter field: ((Clinic.[Clinic Date]<=Now()+6)) but it's opening to the first record created. Where is my flaw?

Thanks so much in advnace for your help.

 

[SkipVought]

Hi,

Well it's TRUE that 06/28/2007 <= <=Now()+6, is it not?

How about
[tt]
MAX(Clinic.[Clinic Date])<=Now()+6))
[/tt]


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

And what about this ?
(Clinic.[Clinic Date]-Now()) Between 0 And 6

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

 

[mizenerman]

SkipVought, you're correct but thank you for understanding what I meant regardless of what I said.

PHV and SkipVought, thank you both for your suggestions but the form is still opening to the first record. But still no luck. Perhaps an On Load event?

 

[PHV]

Me.Filter = "(Clinic.[Clinic Date]-Now()) Between 0 And 6"
Me.FilterOn = True

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

 

[mizenerman]

PHV, you're a rock star. That's what I needed. Thanks.