SQL - How to use a field from AS in Criteria

[LinuxKommy]

This may sound way simplistic, but this SQL statement won't work

SELECT *,CInt(Mid(Product,4,5)) AS Cap FROM LA WHERE Cap=30;

I have absolutely no idea what is wrong...can anyone help me?

thanks,
Douglas

 

[fluteplr]

You can't. The field cap does not exist until the query runs so it cannot be used as part of the selection criteria for the query.

The proper syntax would be.

SELECT *,CInt(Mid(Product,4,5)) AS Cap
FROM LA
WHERE CInt(Mid(Product,4,5)) =30

If you don't actually need to see 'cap' as part of the results of the query you can leave it out of the select clause altogether as in

SELECT *
FROM LA
WHERE CInt(Mid(Product,4,5)) =30

 

[swampBoogie]

SELECT * from
(select la.*,CInt(Mid(Product,4,5)) AS Cap
FROM LA) dt
WHERE Cap =30

 

[r937]

select *, 30 as Cap
from LA
where CInt(Mid(Product,4,5)) = 30