DLookup Between Dates

[crisis2007]

I have tried several variations but I just can not get this to work.


=DLookUp("SpvName","T_Supervisor","Title='Crew Leader' And #" & [Reports]![R_MasterWatch].[txtActivityDate] & "# Between [StartDate]and [EndDate]")

My Table (T_Supervisor)is laid out as follows:
ID Number
SpvName Text
StartDate Date/Time
EndDate Date/Time

What I am trying to retrieve is the name of the supervisor in the table where the date on my report (txtActivityDate) is between the StartDate and the EndDate in my table.

Any assistance is greatly appreciated. I already spent a couple of hours on this and am about to give up.

 

[crisis2007]

Never mind - Thanks anyway. I finally realized the way I had the date formatted in the report was way different than what was in the table which was causing the error.

 

[Darrylles]

Crisis,

Nice, that's how Tek-Tips works best.

You struggle for an hour on something - slap a question in here - get back to analysing the problem.

Either you fix it yourself, or you get an email telling you someone else is also looking at it and may have given you a solution.

Brilliant!

;-)

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.