When changing name of SELECT column with "as [New Name]", how to use New Name in WHERE clause?

[RandV]

This is something that seems simple but I've never quite been able to figure out, I've usually worked around it, but now have a situation where I don't think I can. To put it simply, if in my select clause I have:

SELECT TABLE.COL1 as [New Field]

How do I use [New Field] in the WHERE clause?

WHERE [New Field] > 1;

When I do it look that it prompts me for the parameter for "New field".

To elaborate a little further, what I have at the moment is I'm splitting up a string field to a date field, format YYYYMMDD to M/D/YYYY, then I want to filter that by a date range.

SELECT CDATE( 
MID(SALES_HISTORY_HEADER.IN_DATE, 5, 2) + "/" +
RIGHT(SALES_HISTORY_HEADER.IN_DATE, 2) + "/" +
LEFT(SALES_HISTORY_HEADER.IN_DATE, 4)
) AS [Invoice Date]
WHERE [Invoice Date] between #[Start Date]# and #[End Date]#

But I can't get the query to pick up on [Invoice Date], regardless of how I name it. Any help would be appreciated, thanks.

[orange]

I don't think Access is aware of Invoice Date at the time. Usually you have to repeat the calculation/expression where you first defined it.

you could try (untested)

SELECT [INVOICE DATE] FROM
(
SELECT CDATE( 
MID(SALES_HISTORY_HEADER.IN_DATE, 5, 2) + "/" +
RIGHT(SALES_HISTORY_HEADER.IN_DATE, 2) + "/" +
LEFT(SALES_HISTORY_HEADER.IN_DATE, 4)
) AS [Invoice Date]
)
WHERE [Invoice Date] between #[Start Date]# and #[End Date]#

But why are you NOT storing IN_Date as a Date Datatype?

[RandV]

I don't think Access is aware of Invoice Date at the time. Usually you have to repeat the calculation/expression where you first defined it.

you could try (untested)

SELECT [INVOICE DATE] FROM
(
SELECT CDATE( 
MID(SALES_HISTORY_HEADER.IN_DATE, 5, 2) + "/" +
RIGHT(SALES_HISTORY_HEADER.IN_DATE, 2) + "/" +
LEFT(SALES_HISTORY_HEADER.IN_DATE, 4)
) AS [Invoice Date]
)
WHERE [Invoice Date] between #[Start Date]# and #[End Date]#

But why are you NOT storing IN_Date as a Date Datatype?

Got it, thanks. And not my call with the Date type here, I'm working between two different databases to pull some common data together with a UNION query. One uses proper dates, the other the string.