in Same query need to use calculated Column

[DaRTHY]

Hello friends,

I know my title is looking so complex, actually i could not find right words. Anyway :

in my query i built a mathematics operation :

Result MTB: [T1_alle_Details_pro_Änderung]![FET]+([T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel PET]-[T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel FET])

if i explain with equation => X = A+(B-C) so simple.
this is working very well.

I want to use this "Result MTB" in IIF statement and the problem is beginning here
like this :

PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> Result MTB;"Nicht erreicht";"Ohne Eskalation erreicht")

but its not working. (i tried all combinations with parenthesis or without)

2. try
i wrote new query name is "result" for mathematics operation and created a relationship and wrote at this time

PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> [result]![Result MTB];"Nicht erreicht";"Ohne Eskalation erreicht")

And i got error message : its wrong or too complex....

at least i tried this one :


PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> ([T1_alle_Details_pro_Änderung]![FET]+([T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel PET]-[T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel FET]));"Nicht erreicht";"Ohne Eskalation erreicht"))

of course that one did not work too...


How can i do that correctly. Hope so you can help me asap.

Thank you very much.

 

[namliam]

In access you cant use a calculated column by its alias in the same query, unforatunatly.
Either you have to do the calculation again:

Instead of
PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> X;"Nicht erreicht";"Ohne Eskalation erreicht")

You have to recalculate...
PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> A+(B-C);"Nicht erreicht";"Ohne Eskalation erreicht")

Or use a subquery, where you can use the alias.

 

[spikepl]

Also, you'll be much better off using short names, consisting of letters only
(no spaces or non-alpha), for all you object and field names

 

[DaRTHY]

hi thank you for answer but i recalculated with this formula

PET MTB: IIf([T1_alle_Details_pro_Änderung]![PET]> ([T1_alle_Details_pro_Änderung]![FET]+([T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel PET]-[T81_ursprüngliche Termine Datensaetze identifizieren]![ursprünglicher Ziel FET]));"Nicht erreicht";"Ohne Eskalation erreicht"))

and with subquery you mean : creating new query (i did already name is "result") and using that result query to my original query(name is v_Pet_All_T1) and creating relationship those right ???
i tried too...

 

[DaRTHY]

those names are not my choice. im using a database who had built before me

 

[DaRTHY]

okey thank you namliam i found my fail. i was using () thats why wont work....