Pass Through Query Issue

[Badeye]

Hi Guys,

I've created a Pass Through Query but my problem i have created a calculated field as below:

CASE WHEN(PERS_VISA_EXPIRY_DATE < GETDATE()) THEN 'EXPIRED'
WHEN(PERS_VISA_EXPIRY_DATE > DATEADD(Month,4,GETDATE())) THEN 'OK'
WHEN(PERS_VISA_EXPIRY_DATE < DATEADD(Month,4,GETDATE())) THEN 'About To Expire'
ELSE NULL END AS STATUS

My question is, how so i now do a where clause based on this new field. When i try it tells me that the column is not there. After some reading i figured that SQL works back to front so that would explain why i can't do it but there must be a way. Basically i need to be able to filter by either Expired/About To Expire/OK

If someone could point me in the right direction that would be great. Just one more thing, i will be wanting to select the filter from a combo box on a form if someone could help with that too.

Thanks,

Simon

 

[AccessMSSQL]

You can either create a new View in SQL Server based on your SELECT statement and then base your pass through query on that view. You can also modify pass through query as follows:

Select * FROM
(SELECT field1, field2, CASE...END as Status FROM YOURTABLE) a

*****************
http://www.accessmssql.com