This content has been marked as final. Show 5 replies
I don't believe you can query it like that.. (please correct me if I'm wrong).
But, you could query like that if you did the first select statement and then do a following query to query the first query. So the datasource of the second query would be "query" and you can use the where clause calling that column name.
Your query is crashing because you are using a column alias in the where clause. That can't be done, end of discussion.
Repeating the concatonation in your where clause will probably work. It's certainly worth a shot.
You can't reference an alias in the where clause like that. The usual method is to use the actual table column names in the where clause. Though I would probably convert the value to a valid date or datetime object, assuming the legacy database supports them.
WHERE ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' ....etc.
Another option is to use a derived table, if your db supports them
SELECT t.AdINFO_Publication, t.PubYear
ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear
) AS t
WHERE t.PubYear BETWEEN ....start date... AND ....other date...
Failing that, you could also try using a query of queries as amers suggested.
> AND PubYear BETWEEN #dateformat(startdate,"yyyy-mm-dd")# AND ....
Even if the column did exist, you'd probably get a syntax error because string values need to be enclosed in single quotes.
Thank You so much for all this info. I am going try out these suggestions. THANK YOU!