dlookup in Control Source on a textbox

[DartGuru]

Whilst being familiar with VB, coding within an Access 2003 form is new
to me.

I have a table [Data_Input] with a date and a text field, [Weekending]
and [Overall]. The table has the following records:-

Weekending Overall
27-Oct-06 R
03-Nov-06 G
10-Nov-06 G

I have a form attached to this table, where the user can enter a
Weekending value and the preceding two weeks Overall values get shown.

So I have a textbox on a form, txtWeekending
I have two other textboxes, txtLastWeek, txtWeekBefore,
which calculate the preceding two weeks dates using DateSerial and -7
and -14 on the day parameter respectively, so if I enter a date of
03/11/06, txtLastWeek displays 27/10/06 and txtWeekBefore displays
20/10/06.
This works as expected.

Finally, I have two more textboxes txtLastWeekOverall and
txtWeekBeforeOverall which are meant to pick up the field corresponding
to the approriate week, but the always remain blank.

The Control Source on the txtLastWeekOverall textbox is...
=DLookUp("[Overall]","[Data_Input]","[Weekending]=" &
[txtLastWeekOverall])

I have also tried the following variations...
=DLookup("[Overall]","[Data_Input]","[Weekending]=" &
Forms!txtLastWeekOverall)
=DLookUp("[Overall]","[Data_Input]","[Weekending]=" &
Forms![txtLastWeekOverall])
=DLookup("[Overall]","[Data_Input]","[Weekending]=#" &
Forms!txtLastWeekOverall& "#")
=DLookUp("[Overall]","[Data_Input]","[Weekending]=#" &
Forms![txtLastWeekOverall] & "#")

All to no avail - both the alst two display "#Error, the first two are
blank.

Anyone see what I'm doing wrong ?

Do I need to surround the comparsion value with #[txtLastWeekOverall]#
?

TIA

 

[Guest]

This, I think, should do it. VBA requires some spacing between elements that
SQL does not. Be sure the & has spaces on both sides of it. I have found
that using only the name of a control in this situation seems to work best.

= DLookup("[Overall]", "[Data_Input]", "[Weekending] = #" &
txtLastWeekOverall & "#")

Also, I don' know that the DateSerial is the correct function to use. I
would recommend using DateAdd:

= DateAdd("ww", -1, Me.txtWeekEnding)

 

[DartGuru]

This, I think, should do it. VBA requires some spacing between elements that
SQL does not. Be sure the & has spaces on both sides of it. I have found
that using only the name of a control in this situation seems to work best.

= DLookup("[Overall]", "[Data_Input]", "[Weekending] = #" &
txtLastWeekOverall & "#")

Also, I don' know that the DateSerial is the correct function to use. I
would recommend using DateAdd:

= DateAdd("ww", -1, Me.txtWeekEnding)

Thanks Klatuu, I also need to remove the brackets around [Data_Input],
then it works, cheers.