Dlookup question

[hellohello1]

I am using Access 2003.

I have a table called [KPI Rating] with the fields:
--number field called [Project ID]
--date field called [As-Of Date]
--number field called [Overall KPI Score].

I have another table called [Project Status Survey] with the fields:
--primary key number field called [Project ID]
--primary key date field called [As-Of Date].

I have a query with both tables where the relationship is
[KPI Rating].[Project ID]=[Project Status Survey].[Project ID] and
[KPI Rating].[As-of Date]= [Project Status Survey].[As-of Date].

There is a criteria in the query that says [As-Of Date] >Date()-28. This is done to always get the current's month's data.

I am trying to create a dlookup to find the Overall KPI Score from the previous month. This is what I tried, but it's not working: the result I'm getting is all 3s which is incorrect.

Expr1: DLookUp("[overall KPI Score]","[KPI Rating]","[as-of date] = # " & DateSerial(Year(Date()),Month(Date())-1,0) & "#" And [KPI Rating].[Project ID]=[Project Status Survey].[Project ID])

Help!

Thanks,

 

[hellohello1]

I think i got it:

Expr1: DLookUp("[overall KPI Score]","[KPI Rating]","[KPI Rating].[Project ID]= " & [project status survey].[project id] & " And [as-of date]= # " & DateSerial(Year(Date()),Month(Date())-1,0) & "#")

it was just a matter of putting the correct characters (#, ", &) in the right places.

Yeah.

thanks though.