dlookup in Control Source on a textbox

D

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
 
K

Klatuu

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)
 
D

DartGuru

Klatuu said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top