Multi-Part Identifier Could Not Be bound 1

[FireGeek21]

I have a SQL Expression (not a command) to bring in a field and I am getting an error "Multi-Part Identifier Could Not Be Bound". Any thoughts? Here is my SQL Expression:

(SELECT PAPOSITION.DEPARTMENT
FROM PROD.dbo.PAPOSITION
WHERE PROD.dbo.PAPPOSITION.EFFECT_DATE < MAX(PROD.dbo.PAPOSITION.EFFECT_DATE)-1
and PROD.dbo.PAPOSITION.R_POSITION = HRHISTORY.A_VALUE
and PROD.dbo.PAPOSITION.COMPANY = HRHISTORY.COMPANY
)

PAPOSITION is a table not in the report. HRHISTORY is a table in the report. I have done SQL Expressions before like this but not where I am trying to grab the second most recent EFFECT_DATE.

Thougts???

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[pmax9999]

I have experienced this myself and could never work out what the problem was, other than it most often occurred when using table aliases.

Ken Hamady posted this blog last year. I have not had a the opportunity to test it but having used Ken's tips many times I am sure it would work:

Link

Will be interested to see if it helps.

Cheers
Pete