Using DLookup function in a form to get a specific record from a table 1

[it2009]

I have two tables, a Lot table and Item table. There is a one to many relationship between them, Item table having 1 relationship and Lot table having many relationship. The following are fields in each table:

LOT
------
PK Lot_Num
FK Item_Code
NumCaseScheduled
Batches Needed
Week_Of
Note

Item
-----
PK Item_Code
Film Code
lbsPerCase
Flavor

Next, I have two forms. The first form allowing the users to pick the week_of from a combobox and pick the item they want to produce for that week. Than the user clicks to open the second form. In the Second form, I want the Lot_Num and other Lot information to display automatically if the record exist based on the week_of selected in the first form. I am using the following DLookup in the control source for the Lot_Num in Form2, but it does not seem to work.
=DLookUp("[Lot_Num]","Lot","[Week_Of] =" & [Forms]![Form2]![Week_Of])

Please help me as soon as possible.

Thank you

 

[MajP]

you did not say what does not work
error?
no value?
wrong value?

if week_of is a string

[Week_Of] ='" & [Forms]![Form2]![Week_Of] & "'

 

[it2009]

When I put in the =DLookUp("[Lot_Num]","Lot","[Week_Of] =" & [Forms]![Form2]![Week_Of]) into control source for Lot in Form2, I get #error.

In the Lot Table
Week_Of is Date/Time Data Type
Lot_Num is Auto Number Data Type

Thanks

 

[randy700]

Week_Of is Date/Time Data Type

You need qualifiers. Perhaps...

=DLookUp("[Lot_Num]","Lot","[Week_Of] =[b][COLOR=red] #[/color][/b]" & [Forms]![Form2]![Week_Of][b][COLOR=red] & "#"[/color][/b])

Randy