Trouble with Dlookup Calculated Field in Query

[ozop102]

Hello All,

I am having an issue using calculated fields in a query with Dlookup.

I have a query that needs to pull data from another table based on another field in the same query.

Exp1 in query:

SCOD Due: DLookUp("[SCOD Date]","SCODS","[Rank] = " & [CAL Table]![Rank])

I want the SCOD Date from the SCODS table. On the SCODS table, the dates are in DD Mmm format separated by rank.

Example of what I am trying to accomplish.

SCODS Table:
Rank SCOD Date
SSG 31 MAY

ACA Listing Query ([CAL Table]):
Rank Exp1
SSG 31 May

Then

Exp2
Exp1 & Year(Date())

31 May 2019

All this so that I can calculate due dates based on the final date in Exp2

Running this query currently leaves each field with #Error .

Any help or guidance would be greatly appreciated!