Dlookup find previous record does not work

[ppataki]

Dear All,

I have an aggregate query that displays certain fields of a table
I would like to display the values of the previous record of one of the fields

I have used this formula:

Expr1: DLookUp("[creditlimitcp]";"[tbl_annualdata]";"[yeardata] = " & [yeardata]-1)

but it only works for one "group" and copies that data for all the others
I attached a screenshot to better understand the problem (circled the part that works)
Could you please advise?
Many thanks in advance

 

[RuralGuy]

It looks to me like your criteria is not adequate to properly select the record you want.

 

[ppataki]

What other criterion shall I use?
Thank you in advance!

 

[RuralGuy]

Try:

Expr1: DLookUp("[creditlimitcp]";"[tbl_annualdata]";"[Custname] = " & [CustName] & " AND [yeardata] = " & [yeardata]-1)

 

[ppataki]

I get #Error values in the field...any suggestions please?
Thank you

 

[RuralGuy]

I forgot CustName was a string:

Expr1: DLookUp("[creditlimitcp]";"[tbl_annualdata]";"[Custname] =[B][COLOR="Red"] '[/COLOR][/B]" & [CustName] & "[B][COLOR="Red"]'[/COLOR][/B] AND [yeardata] = " & [yeardata]-1)