Dlookup question

[rutica]

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

A database with sample data is attached.
Help!

Thanks,

 

[vbaInet]

Are we talking about a DCount or a DLookup here?

 

[SOS]

Go to the revised sample. I modifed your query and added one to use for the previous month's values.

 

[rutica]

vbaInet: I'm talking about Dlookup (not DCount).

SOS: I looked at your database. thank you for that! but is there a way to do what i want without requiring an additional query? also, i spent so much time trying to figure out the dlookup that i am really curious as to what i did wrong.

Thanks,

 

[rutica]

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.