set value and use in a expression

[Ghena]

Hi to everybody.

The problem is very simple, I suppose:

I need to use this value:

IIf([store_2.ID_ROOM]=1,[@sgl],IIf([store_2.ID_ROOM]=2,[@dbl],[@tpl]))
AS valore

in the following espression:

IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE

At the moment is not possible to execute the query becouse

the expression:

IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE

seems to be not included in the GROUP BY:

This is the whole query.


SELECT hotel.ID_hotel, hotel.insegna, store_2.ID_room,
IIf(IsNull([TOT_PAX]),0,[TOT_PAX]) AS TOT, store_2.allottment,
IIf([store_2.ID_ROOM]=1,[@sgl],IIf([store_2.ID_ROOM]=2,[@dbl],[@tpl]))
AS valore,
IIf(DateAdd('d',[realease],Date())>=#5/28/2006#,IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE, store_2.realease, DateAdd('d',[realease],Date()) AS
stato, camere.tipo_room
FROM ((hotel INNER JOIN store_2 ON hotel.ID_hotel = store_2.ID_hotel)
LEFT JOIN store1 ON (store_2.ID_room = store1.id_room) AND
(store_2.ID_hotel = store1.ID_hotel)) INNER JOIN camere ON
hotel.ID_hotel = camere.ID_hotel
GROUP BY hotel.ID_hotel, hotel.insegna, store_2.ID_room,
IIf(IsNull([TOT_PAX]),0,[TOT_PAX]), store_2.allottment,
IIf(DateAdd('d',[realease],Date())>=#5/28/2006#,IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore])),
store_2.realease, camere.tipo_room
HAVING (((store_2.ID_room) In (3,1)));

thanks a lot.

 

[Tom Ellison]

Dear Ghena:

SELECT hotel.ID_hotel, hotel.insegna, store_2.ID_room,
Nz(tot_pax, 0) AS TOT, store_2.allottment,
IIf(store_2.ID_ROOM = 1, [@sgl], IIf(store_2.ID_ROOM = 2, [@dbl],
[@tpl]))
AS valore,
IIf(DateAdd('d', realease, Date()) >= #5/28/2006#,
Nz([tot_pax], 0) - valore),
allottment - Nz(tot_pax, 0) - valore)
AS DISPREALE,
store_2.realease, DateAdd('d', realease, Date()) AS stato,
camere.tipo_room
FROM ((hotel
INNER JOIN store_2
ON hotel.ID_hotel = store_2.ID_hotel)
LEFT JOIN store1
ON (store_2.ID_room = store1.id_room)
AND (store_2.ID_hotel = store1.ID_hotel))
INNER JOIN camere
ON hotel.ID_hotel = camere.ID_hotel
GROUP BY hotel.ID_hotel, hotel.insegna, store_2.ID_room,
realease, tot_pax, valore, allottment,
store_2.realease, camere.tipo_room
HAVING store_2.ID_room In (3,1);

Having gotten this far trying to fix things up, I still see a difficulty.
You have a column you calculate in the SELECT clause aliased "valore". It
appears you then attempt to use this within the same query. You cannot do
this! A calculated value is not available within that same query. You must
calculate it again each time. So, I'll try to make some more changes
without any errors (I hope).

SELECT hotel.ID_hotel, hotel.insegna, store_2.ID_room,
Nz(tot_pax, 0) AS TOT, store_2.allottment,
IIf(store_2.ID_ROOM = 1, [@sgl], IIf(store_2.ID_ROOM = 2, [@dbl],
[@tpl]))
AS valore,
IIf(DateAdd('d', realease, Date()) >= #5/28/2006#,
Nz([tot_pax], 0),
allottment - Nz(tot_pax, 0)) -
IIf(store_2.ID_ROOM = 1, [@sgl], IIf(store_2.ID_ROOM = 2, [@dbl],
[@tpl]))
AS DISPREALE,
store_2.realease, DateAdd('d', realease, Date()) AS stato,
camere.tipo_room
FROM ((hotel
INNER JOIN store_2
ON hotel.ID_hotel = store_2.ID_hotel)
LEFT JOIN store1
ON (store_2.ID_room = store1.id_room)
AND (store_2.ID_hotel = store1.ID_hotel))
INNER JOIN camere
ON hotel.ID_hotel = camere.ID_hotel
GROUP BY hotel.ID_hotel, hotel.insegna, store_2.ID_room,
realease, tot_pax, [@sgl], [@dbl], [@tpl], allottment,
store_2.realease, camere.tipo_room
HAVING store_2.ID_room In (3,1);

I don't know how much confidence I put in this. But here's a rundown of the
steps I've taken.

I removed unnecessary brackets and parens. Nothing critical there.

I replaced "valore" with the calculation used for it. You cannot use the
column alias to represent this calculation with the same query.

I used Nz() to replace IIf(IsNull( constructs for simplicity.

I replaced the long calculations in the GROUP BY with just those columns
used in the calculations.

It is my hope that you can use this to fix up what you have. Without the
ability to test it, this is a tough one!

Tom Ellison

Hi to everybody.

The problem is very simple, I suppose:

I need to use this value:

IIf([store_2.ID_ROOM]=1,[@sgl],IIf([store_2.ID_ROOM]=2,[@dbl],[@tpl]))
AS valore

in the following espression:

IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE

At the moment is not possible to execute the query becouse

the expression:

IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE

seems to be not included in the GROUP BY:

This is the whole query.


SELECT hotel.ID_hotel, hotel.insegna, store_2.ID_room,
IIf(IsNull([TOT_PAX]),0,[TOT_PAX]) AS TOT, store_2.allottment,
IIf([store_2.ID_ROOM]=1,[@sgl],IIf([store_2.ID_ROOM]=2,[@dbl],[@tpl]))
AS valore,
IIf(DateAdd('d',[realease],Date())>=#5/28/2006#,IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore]))
AS DISPREALE, store_2.realease, DateAdd('d',[realease],Date()) AS
stato, camere.tipo_room
FROM ((hotel INNER JOIN store_2 ON hotel.ID_hotel = store_2.ID_hotel)
LEFT JOIN store1 ON (store_2.ID_room = store1.id_room) AND
(store_2.ID_hotel = store1.ID_hotel)) INNER JOIN camere ON
hotel.ID_hotel = camere.ID_hotel
GROUP BY hotel.ID_hotel, hotel.insegna, store_2.ID_room,
IIf(IsNull([TOT_PAX]),0,[TOT_PAX]), store_2.allottment,
IIf(DateAdd('d',[realease],Date())>=#5/28/2006#,IIf(IsNull([tot_pax]-([valore])),0-[valore],[tot_pax]-[valore]),IIf(IsNull([allottment]-[tot_pax]-[valore]),[allottment]-[valore],[allottment]-[tot_pax]-[valore])),
store_2.realease, camere.tipo_room
HAVING (((store_2.ID_room) In (3,1)));

thanks a lot.