Dlookup multiple criteria

[Apple08]

Hi everyone

Please could someone help me to check the below Dlookup formula:

PreviousRAGid: DLookUp("[CurrentRAGid]","strategicProjectFinance","[strategicProjectFinanceID]=" & [strategicProjectFinanceID] & " And [monthID]=#" & DateSerial(Year([monthID]),Month([monthID]),0) & "#")

I want the previous month RAGid from the above formula, but it doesn't work. It turns out with nothing in the PreviousRAGid field. Have I done something wrong?

 

[xenou]

What kind of values do you have for MonthID? Is that an actual date value? Or a numeric ID? Or actually a text value? Same might be asked of FinanceID.

 

[Apple08]

Thanks for trying to help.

monthID is actual date, it's always the end of a month such as 31/07/2016
financeID is auto number which is unique
strategicProjectFinance is the query name
CurrentRAGid is in number format

 

[xenou]

Test your dlookup using a sql statement.

select * from strategicProjectFinance where strategicProjectFinanceID = 1 and monthID = #31/07/2016#

 

[Apple08]

I have changed the ID to 9 instead of 1 as 1 was in June. However there is no value for the Previous RAG though.

 

[xenou]

On the face of it, it seems that the query is correct and return no value because no value exists matching your criteria. What are you expecting it to return? What data is in the tables? What does the query you are querying do (strategicProjectFinance ) - does it also have the record you are looking for?

 

[Apple08]

I have currentRAGid in the strategicProjectFinance query, with RAG status from 1 to 3. All I want the return is to look for last month's currentRAGID and auto input as this month's previousRAGid. So if the June's currentRAGID is 2, then in July, the previousRAGid will be showing as 2 automatically.

 

[Apple08]

The currentRAGid in June should be 1, 2 and 4 and I expect a return of 1,2 and 4 in the previousRAGid in July, however it returns all in 1 after I modify as below:

PreviousRAGid: DLookUp("[CurrentRAGid]","strategicProjectFinance","[strategicProjectFinanceID]=" & [strategicProjectFinanceID] & "# " And "[monthID]=#" & DateSerial(Year([monthID]),Month([monthID]),0) & "#")

 

[xenou]

expect a return of 1,2 and 4 in the previousRAGid in July

A DLookup function cannot return multiple values. Just one. You may be seeing the first value here.

 

[Apple08]

I see, many thanks for your help.