Query w/a couple of derived fields can I query within

[goslincm]

I have a query that has a couple derived fields:

F65Due: Format(DateSerial([Year],[FiscalEndMM]+7,0),"mm/dd/yyyy")

Status: IIf([received]>[F65Due],"LATE",IIf([received]<[F65Due],"TIMELY"))

What I want to do it pull those F65Due dates of say 9/30/06 as well a Status of LATE.

I'm not sure its possible

 

[lespaul]

you can't use an alias (F65Due) in the same query where it's declared. You have to use the FORMULA for the alias:

SELECT Format(DateSerial([Year],[FiscalEndMM]+7,0),"mm/dd/yyyy") AS F65Due, 
IIf([received]>Format(DateSerial([Year],[FiscalEndMM]+7,0),"mm/dd/yyyy"),"LATE","TIMELY")

Additionally you don't need the second if. The condition is either Received > OR it's not (what about = to?).

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Leslie, thank you so much. I learn so much out here from everyone. I appreciate the explanation of why you can't do something, as well as you showing me how to do it.

 

[goslincm]

Leslie, did I speak too soon. How do I filter out those with a 9/30/06 date that are late within the query?

 

[lespaul]

Not sure I understand what you mean? You don't want to include records where the date is 9/30/06? Can you post all the SQL?

Thanks!

 

[goslincm]

From the recordset this query produces, I need to further filter to the level where I can filter by [F65Due].

So in the end, I could tell it I want to see those with a 9/30/07 F65Due date that are also "LATE"...

I hope I'm being a little clearer for you. Here is the query




SELECT F65.countycd, F65.localunittype, F65.localunitcd, F65.Year, Format(DateSerial([Year],[FiscalEndMM]+7,0),"mm/dd/yyyy") AS F65Due, F65.Received, IIf([received]>[F65Due],"LATE",IIf([received]<[F65Due],"TIMELY")) AS Filed, F65.Electronic, F65.[1st Letter], F65.[2nd Letter], F65.Note, F65.Extension, F65.[Revenue Sharing], dbo_LocalUnit.FiscalEndMM

FROM F65 INNER JOIN dbo_LocalUnit ON (F65.localunitcd = dbo_LocalUnit.LocalUnitCd) AND (F65.localunittype = dbo_LocalUnit.LocalUnitType) AND (F65.countycd = dbo_LocalUnit.CountyCd);