referencing Calculated fields in Subqueries..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In one of my calculated fields, I am trying to reference another calculated
field within it:

EMPIDValidation: IIf(Len([EMPID])<>0,[EMPID],(Select A.EMPID FROM
tblInvalidSINMapping as A WHERE (A.InvalidSIN = tblPayrollUpload.SIN) AND
(A.NewStore = tblPayrollUpload.Store)))

ECodeValidation: (Select A.Earnded FROM CPEMPD AS A WHERE (A.EMPLOYEE =
[EMPIDValidation]) AND (A.EARNDED = ECode))

When I try to run the query, it is asking for a parameter value for
EMPIDValidation. What am I doing wrong here?
 
Depending on the timing of how Access executes your query, you may not be
able to use the other calculated field in a further expression.

As a simple example, if this is a Totals query with Where in the Total row,
Access won't have the other expression computed until it runs the whole
query and performs the GROUP BY, but the WHERE must be executed before the
GROUP BY.

You may be able to redesign the query, or you may need to repeat the entire
expression (including the subquery) instead of using its alias.
 
Thanks. that makes sense...thanks again.

Allen Browne said:
Depending on the timing of how Access executes your query, you may not be
able to use the other calculated field in a further expression.

As a simple example, if this is a Totals query with Where in the Total row,
Access won't have the other expression computed until it runs the whole
query and performs the GROUP BY, but the WHERE must be executed before the
GROUP BY.

You may be able to redesign the query, or you may need to repeat the entire
expression (including the subquery) instead of using its alias.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Steven Cheng said:
In one of my calculated fields, I am trying to reference another
calculated
field within it:

EMPIDValidation: IIf(Len([EMPID])<>0,[EMPID],(Select A.EMPID FROM
tblInvalidSINMapping as A WHERE (A.InvalidSIN = tblPayrollUpload.SIN) AND
(A.NewStore = tblPayrollUpload.Store)))

ECodeValidation: (Select A.Earnded FROM CPEMPD AS A WHERE (A.EMPLOYEE =
[EMPIDValidation]) AND (A.EARNDED = ECode))

When I try to run the query, it is asking for a parameter value for
EMPIDValidation. What am I doing wrong here?
Click to expand...
Click to expand...
 
Back
Top