Reference a field created in same query (1 Viewer)

[jp_viper]

Greetings.

In this query called qryMainList, I am trying to create the criteria on the Equipment_Name field requiring the date difference between today's date and the field called NextDueDate (that I just calculated within the same query) be less than 1/10 of the Interval field. NextDueDate shows as expected in a table, but I am having trouble referencing it in the criteria for Equipment_Name. Here are the different outcomes depending on how I tried to input NextDueDate in DateDiff():

DateDiff("y", NextDueDate, Date() ) < (0.1 * [Task_List].[Interval]) - NextDueDate automatically gets converted to a string as you can see underlined below, giving an error on datasheet view for incorrect parameter type.

DateDiff("y", [NextDueDate], Date() ) < (0.1 * [Task_List].[Interval]) or DateDiff("y", [qryMainList].[NextDueDate], Date() ) < (0.1 * [Task_List].[Interval]) - Entering datasheet view will prompt me for the value of NextDueDate or qryMainList.NextDueDate, as can be seen below.

Do I need to make NextDueDate part of a table? I would prefer to keep it a field within this query. Should I just embed the expression for NextDueDate within DateDiff and not reference another field? *Update: This worked, though it would look cleaner if I referenced NextDueDate but certainly not needed for my purposes.
Thanks.

 

[bob fitz]

Perhaps your criteria should be in a different column

 

[plog]

You need to make a custom field and apply criteria to that custom field. Essentially you put your criteria inside an iif and have the iif return a value that you use for criteria. I will use a simplified example:

IsValid: Iif(Equipment_Name < NextDueDate, 1, 0)

Then underneath that in the criteria area you put 1. Your criteria will be more involved and include the DateDiff but it goes in the same place the comparison in the above example is.

Also, it seems odd that a field called 'Equipment_Name' would be a number, but perhaps that is correct.

 

[plog]

Looking at it closer, you cannot reference a calculated field in the same query you calculate it. You can still use the method I outlined above, but you wouldn't use NextDueDate, you would use the calculation that is involved in it.

It's algebraic replacement. If A = B + C, you can just substitute B+C wherever you see A. Same with the calculation of NextDueDate.

 

[ebs17]

To see, switch to SQL view - ultimately the SQL statement will have to be executed.

In the order of query processing, the WHERE part is executed before the SELECT part. This means that a field name “NextDueDate”, created by aliasing, is unknown. This is where the parameter query comes from.
So you have to use the calculation expression in the WHERE part instead of the field name.

If you publish the SQL statement as code, they can simply show the fix and provide it to you.

 

[jp_viper]

You need to make a custom field and apply criteria to that custom field. Essentially you put your criteria inside an iif and have the iif return a value that you use for criteria. I will use a simplified example:

IsValid: Iif(Equipment_Name < NextDueDate, 1, 0)

Then underneath that in the criteria area you put 1. Your criteria will be more involved and include the DateDiff but it goes in the same place the comparison in the above example is.

Also, it seems odd that a field called 'Equipment_Name' would be a number, but perhaps that is correct.

Thanks, I see the issue with how I treated criteria. I made a new field with IsValid: IIf(DateDiff("d",Date(),DateAdd("d",[Task_List].[Interval],[Task_Log].[Date1]))<0.1*[Task_List].[Interval],1,0) and set criteria to 1. Does what I wanted it to.

Still, my original issue stands when I tried to use NextDueDate in the new field I created (as a substitute for DateAdd).

 

[jp_viper]

To see, switch to SQL view - ultimately the SQL statement will have to be executed.

In the order of query processing, the WHERE part is executed before the SELECT part. This means that a field name “NextDueDate”, created by aliasing, is unknown. This is where the parameter query comes from.
So you have to use the calculation expression in the WHERE part instead of the field name.

I guess my mistake was treating Criteria as a WHERE clause then. Is WHERE only available through SQL?

 

[plog]

Still, my original issue stands when I tried to use NextDueDate in the new field I created (as a substitute for DateAdd).

You can't calculate and use a field in the same query. So, either create a new query based on the one you have which will let you work with NextDueDate, or use the underlying calcualtion that goes into NextDueDate wherever you want to use NextDueDate.

Suppose you have a table with fields A, B, C and you want to add them together and then use criteria:

NewFieldZ = A + B

NewFieldY = NewFieldZ / C

NewFieldY will not work in the same query in which you defined NewFieldZ. So instead you replace NewFieldZ with what its calculated with:

NewFieldY = (A+B) / C

 

[Pat Hartman]

Different implementations of SQL syntax work differently. Access SQL does not allow you to reference a field created in the query in any other expression or query clause. But, I think T-SQL does allow this.

Just follow @plog 's advice

 

[June7]

Can reference a calculated field in same query that creates it, just not in WHERE clause. As example:

SELECT Qty * Price AS Cost, Cost * 0.15 AS Comm FROM Orders;

If you want to see all records where Comm is >= 25:

WHERE Qty * Price * 0.15 >= 25