IIF and query booking

G

Ghena

Hi...
I'm developing a simple query to check availability in an hotel booking
engine

But i have just some problem with IIF

SELECT hotel.Id_htl, hotel.localita, dispo.dal, dispo.al,
dispo.allottment, dispo.realease, prenotazioni.dal, prenotazioni.al,
IIf(IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
),0,IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
) AS tot
FROM prenotazioni, dispo INNER JOIN hotel ON dispo.id_htl =
hotel.Id_htl
GROUP BY hotel.Id_htl, hotel.localita, dispo.dal, dispo.al,
dispo.allottment, dispo.realease, prenotazioni.dal, prenotazioni.al
HAVING (((hotel.localita)="Roma") AND ((dispo.dal)<=#6/21/2005#) AND
((dispo.al)>=#6/24/2005#) AND ((prenotazioni.dal)<=#6/21/2005#) AND
((prenotazioni.al)>=#6/24/2005#));

The error is : Wrong Argument in the query:
IIf(IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
),0,IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
)

could you help me.

Thanks a lot.
 
D

Dale Fye

Ghena,

In one section of your query you have the dates formatted D/M/YYYY (which
Access will not interpret properly) and in the other portion , you have the
date formatted as M/D/YYYY (which Access will interpret properly). No how
you format these dates for display purposes, in the query, they must be
entered as M/D/YYYY.

HTH

Dale
 
V

Vincent Johns

It looks as if your problem is with the IsNull(), not the IIF. In any
event, I wasn't sure what you were trying to do with your Query.
(Sample data, plus a description or example of what you'd like to see
from them, would have helped.)

Anyway, my guess as to what you're trying to do, and my suggested
answer, follow. Suppose your 3 Tables contain data like these:

[Hotel] Table Datasheet View:

Id_htl localita
---------- --------
-527725142 Torino
792832540 Roma
1214254829 Venezia

[dispo] Table Datasheet View:

dispo_ID dal al allottment realease id_htl
---------- --------- --------- ---------- -------- ---------
1917756131 6/20/2005 6/26/2005 2 3 792832540

[prenotazioni] Table Datasheet View:

prenotazioni_id dal al sell_q id_htl
--------------- --------- --------- -------- ---------
-685871159 6/20/2005 6/26/2005 $70.00 792832540
-301450154 6/23/2005 6/26/2005 $40.00 792832540
1206555624 6/20/2005 6/26/2005 $100.00 792832540

Then a Query that will total the values of the [sell_q] fields in the
selected records might look like this:

[Query1] SQL:

SELECT hotel.Id_htl, hotel.localita, dispo.dal,
dispo.al, dispo.allottment, dispo.realease,
prenotazioni.dal, prenotazioni.al, Sum(prenotazioni.sell_q) AS tot
FROM prenotazioni, dispo INNER JOIN hotel
ON dispo.id_htl = hotel.Id_htl
WHERE (((prenotazioni.id_htl)=[hotel].[Id_htl]))
GROUP BY hotel.Id_htl, hotel.localita, dispo.dal,
dispo.al, dispo.allottment, dispo.realease,
prenotazioni.dal, prenotazioni.al
HAVING (((hotel.localita)="Roma")
AND ((dispo.dal)<=#6/21/2005#) AND ((dispo.al)>=#6/24/2005#)
AND ((prenotazioni.dal)<=#6/21/2005#)
AND ((prenotazioni.al)>=#6/24/2005#));

The results of running this Query on my example data would be the
following (one record, split into two parts to fit onto the page). Note
that [prenotazioni] record number -301450154 is omitted because its
[dal] field is not in the selected range.

[Query1] Query Datasheet View:

Id_htl localita dispo.dal dispo.al allottment realease
--------- -------- --------- --------- ---------- --------
792832540 Roma 6/20/2005 6/26/2005 2 3

prenotazioni.dal prenotazioni.al tot
---------------- --------------- -------
6/20/2005 6/26/2005 $170.00


Hi...
I'm developing a simple query to check availability in an hotel booking
engine

But i have just some problem with IIF

SELECT hotel.Id_htl, hotel.localita, dispo.dal, dispo.al,
dispo.allottment, dispo.realease, prenotazioni.dal, prenotazioni.al,
IIf(IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
),0,IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
) AS tot
FROM prenotazioni, dispo INNER JOIN hotel ON dispo.id_htl =
hotel.Id_htl
GROUP BY hotel.Id_htl, hotel.localita, dispo.dal, dispo.al,
dispo.allottment, dispo.realease, prenotazioni.dal, prenotazioni.al
HAVING (((hotel.localita)="Roma") AND ((dispo.dal)<=#6/21/2005#) AND
((dispo.al)>=#6/24/2005#) AND ((prenotazioni.dal)<=#6/21/2005#) AND
((prenotazioni.al)>=#6/24/2005#));

The error is : Wrong Argument in the query:
IIf(IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
),0,IsNull(select sum(sell_q) from prenotazioni Where
id_htl=[hotel]![id_htl] and dal <= #21/06/2005# and al >=#24/06/2005#
)

could you help me.

Thanks a lot.
Click to expand...
 
G

Ghena

wow... thanks for your attention.

but is not exactly the results qury give to me.

My target is to display every hotel the relative allottment and
realese. ( in a specific period) and then check how many rooms has been
sold.

But now i prefer improve it to check also the number of persons per
room.

So Actually I have this one:


SELECT hotel.Id_htl, hotel.localita, dispo.allottment, dispo.realease,
(SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#)) AS
totroom, dispo.dal, dispo.al, camere.min_pax, camere.max_pax
FROM prenotazioni, camere INNER JOIN (dispo INNER JOIN hotel ON
dispo.id_htl = hotel.Id_htl) ON camere.id_hotel = dispo.id_htl
GROUP BY hotel.Id_htl, hotel.localita, dispo.allottment,
dispo.realease, dispo.dal, dispo.al, camere.min_pax, camere.max_pax
HAVING (((hotel.localita)="Roma") AND ((dispo.dal)<=#6/20/2005#) AND
((dispo.al)>=#6/22/2005#));


Dispo = availability table
In this way Get the results as below:


Id_htl localita allottment realease totroom dal al min_pax max_pax
24 Roma 6 7 2 02/06/2005 30/06/2005 1 2
25 Roma 10 7 2 16/06/2005 30/06/2005 1 2
26 Roma 10 7 2 16/06/2005 30/06/2005 1 2
27 Roma 10 7 2 16/06/2005 30/06/2005 1 2


My problem is totroom.

Becouse it rapresents and repeat the value for id_htl=24

I want to get only the right value for for each row.
Thanks a lot.
 
G

Ghena

Perhaps is better before solve the subquery:

SELECT hotel.Id_htl, (SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#) and
id_htl=[id_htl] ) AS Espr1
FROM prenotazioni, hotel
GROUP BY hotel.Id_htl, (SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#) and
id_htl=[id_htl] );

In this way access give me a Sintax error
Why?
 
V

Vincent Johns

You can get rid of the syntax error message by deleting your GROUP BY
clause, making the SQL of your Query look like this:

SELECT [hotel].[Id_htl], (SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#) and
id_htl=[id_htl] ) AS Espr1
FROM prenotazioni, hotel;

However, I'm not sure that this will do anything useful for you. I was
not able to produce the same output that you posted (for example, with
[Id_htl] = 24, a value that I never used in my examples) based on the
values in my sample Tables.

What would help me analyze your problem is a set of sample records from
each of your Tables, and a datasheet showing the results that you would
like to see based on those Tables. (The datasheet you posted, I think,
is one containing wrong values produced by your current Queries.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Perhaps is better before solve the subquery:

SELECT hotel.Id_htl, (SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#) and
id_htl=[id_htl] ) AS Espr1
FROM prenotazioni, hotel
GROUP BY hotel.Id_htl, (SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#) and
id_htl=[id_htl] );

In this way access give me a Sintax error
Why?
wow... thanks for your attention.

but is not exactly the results qury give to me.

My target is to display every hotel the relative allottment and
realese. ( in a specific period) and then check how many rooms has been
sold.

But now i prefer improve it to check also the number of persons per
room.

So Actually I have this one:


SELECT hotel.Id_htl, hotel.localita, dispo.allottment, dispo.realease,
(SELECT Sum(sell_q) FROM prenotazioni WHERE
(prenotazioni.dal>=#6/20/2005# And prenotazioni.dal<=#6/22/2005#) or
(prenotazioni.al >=#6/20/2005# And prenotazioni.al<=#6/22/2005#)) AS
totroom, dispo.dal, dispo.al, camere.min_pax, camere.max_pax
FROM prenotazioni, camere INNER JOIN (dispo INNER JOIN hotel ON
dispo.id_htl = hotel.Id_htl) ON camere.id_hotel = dispo.id_htl
GROUP BY hotel.Id_htl, hotel.localita, dispo.allottment,
dispo.realease, dispo.dal, dispo.al, camere.min_pax, camere.max_pax
HAVING (((hotel.localita)="Roma") AND ((dispo.dal)<=#6/20/2005#) AND
((dispo.al)>=#6/22/2005#));


Dispo = availability table
In this way Get the results as below:


Id_htl localita allottment realease totroom dal al min_pax max_pax
24 Roma 6 7 2 02/06/2005 30/06/2005 1 2
25 Roma 10 7 2 16/06/2005 30/06/2005 1 2
26 Roma 10 7 2 16/06/2005 30/06/2005 1 2
27 Roma 10 7 2 16/06/2005 30/06/2005 1 2


My problem is totroom.

Becouse it rapresents and repeat the value for id_htl=24

I want to get only the right value for for each row.
Thanks a lot.
Click to expand...
Click to expand...
 
V

Vincent Johns

Certainly you may, but please remove any sensitive data first. I would
also like to see a list (datasheet view) of the kind of output that you
would like to see if the Queries were running as you would like them to.
And please be aware that I may not answer you promptly -- it may be a
few days.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

booking query 2 1
sum if and between dates 2
Crosstab query will work one way but not the other 2
weird query 1
help with a query syntax and grouping 1
Ranking, subqueries and update query problem 11
The whole story - Query problem 6
ntdll crash and allocateheap problem 0

Top