Calculated fields in queries

  • Thread starter Thread starter James Warburton
  • Start date Start date
J

James Warburton

I'm having trouble getting a calculated field ((A) below)
to work that I have in a query, and that itself is based
on two other calculated fields in the query ((B) and (C)
below).

(A)
Suppress?: IIf([Lone Pre Op?]="Lone Pre Op" And [Book Ops
& Pre Ops?]="Book Ops & Pre Ops","Suppress","Show")

(B)
Lone Pre Op?: IIf(IsNull([dbo_tblOperationSlots.dtDate])
And Not IsNull([dbo_tblOperationSlots.dtPreOpDate]),"Lone
Pre Op","Not Lone Pre Op")

(C)
Book Ops & Pre Ops?: IIf([dbo_tblHealthTrusts.txtName]
="North West London Hospitals NHS Trust" Or
[dbo_tblHealthTrusts.txtName]="The Lewisham Hospital NHS
Trust" Or [dbo_tblHealthTrusts.txtName]="Epsom and St.
Helier NHS Trust" Or [dbo_tblHealthTrusts.txtName]="St
Marys NHS Trust" Or (([dbo_tblSpeciality.txtName]="Ophth")
And ([dbo_tblHealthTrusts.txtName]="King's College
Hospital Trust")),"Book Ops & Pre Ops","Pre Ops Only /
Duals")

When I try to run the query I just get one of those "Enter
Parameter Value" boxes come up, which is asking for a
value for the field [Lone Pre Op?], when I just wanted (A)
to draw a value for [Lone Pre Op?] from (B) (and for [Book
Ops & Pre Ops?] from (C) ..)

Is there a problem with the syntax in field (A)?

Thanks in advance.
 
James Warburton said:
I'm having trouble getting a calculated field ((A) below)
to work that I have in a query, and that itself is based
on two other calculated fields in the query ((B) and (C)
below).
Click to expand...

You can't refer to the alias assigned to an expression to use in another
expression. You have to repeat the expression inside of the new one.

EX:

This doesn't work...
***************
FirstExpr: [A] +
SecondExpr: [FirstExpr] * 5

This does...
**********
FirstExpr: [A] +
SecondExpr: ([A] + ) * 5

You can however create a query with FirstExpr and then feed that query into
a second query and use [FirstExpr] * 5 with no problem.
 
Back
Top