Adjusting SQL in Query

A

Ange Kappas

Hi All,
I've progressed a filter in a query of mine TODAY CHARGES to
lookup up records according to the following SQL:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL, RESPEL.DAYS,
RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE, RESPEL.RESNO,
RESPEL.RESNAME, RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND ((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY] And
([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

What I want to do either in Design View or adding something to the above SQL
is:

When in the RESPEL.PRICELIST is NULL to place in the field PRICELIST.PRICE
of the Query TODAY CHARGES the value from the table RESPEL.DAILY CHARGE.

In other words if the field PRICELIST in the Table RESPEL does not have a
value not to lookup the value from the price list as it does in the above
SQL but to take the value from the table RESPEL from the field DAILY CHARGE.

Much Appreciated if someone can help me on this.

It's probably quite a simple inclusion in the Design View on the field
PRICELIST.PRICE in the criteria section, but I need someone to push me in
the right direction.

Thanks

Ange
 
A

Allen Browne

Try typing an expression like this into a blank column in the Field row, in
query design:
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE], PRICELIST.PRICE)

The SQL view would be as below. Note some other changes here as well:
a) The sub-select used the same query as in the main query.
You need to alias this (as shown.)

b) DATE is a reserved word. Hence square brackets added.

c) Several other field names are reserved words too. Although you can bet
around it in this context with the square brackets, you might want to avoid
these names when designing your tables. Examples here are Date, Name, Start,
and End. For a more comprehensive list of names to avoid, see:
http://allenbrowne.com/AppIssueBadWord

Haven't checked all the brackets here, but this kind of thing:

SELECT RUNDATE.[DATE],
RESPEL.ROOMNO,
RESPEL.SURNAME,
RESPEL.[NAME],
RESPEL.COMPANY,
PRICELIST.PRICE,
RESPEL.ARRIVAL,
RESPEL.DAYS,
RESPEL.DEPARTURE,
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE],
PRICELIST.PRICE) AS ThePrice,
RESPEL.TYPECHARGE,
RESPEL.RESNO,
RESPEL.RESNAME,
RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST
ON RESPEL.PRICELIST = PRICELIST.CATEGORY
WHERE ((RUNDATE.[DATE] >= [PRICELIST]![START]
And (RUNDATE.[DATE]) <= [PRICELIST]![END])
AND ((RESPEL.DEPARTURE) >
(SELECT Dupe.[DATE] FROM [RUNDATE] AS Dupe))
AND ((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY]) = [PRICELIST]![COMPANY]
And ([RESPEL]![COMPANY])=[PRICELIST]![COMPANY])
AND (([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]
And ([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

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

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

Ange Kappas said:
Hi All,
I've progressed a filter in a query of mine TODAY CHARGES
to lookup up records according to the following SQL:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL, RESPEL.DAYS,
RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE, RESPEL.RESNO,
RESPEL.RESNAME, RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND
((RESPEL.STATUS)="IN") AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY] And
([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

What I want to do either in Design View or adding something to the above
SQL is:

When in the RESPEL.PRICELIST is NULL to place in the field PRICELIST.PRICE
of the Query TODAY CHARGES the value from the table RESPEL.DAILY CHARGE.

In other words if the field PRICELIST in the Table RESPEL does not have a
value not to lookup the value from the price list as it does in the above
SQL but to take the value from the table RESPEL from the field DAILY
CHARGE.

Much Appreciated if someone can help me on this.

It's probably quite a simple inclusion in the Design View on the field
PRICELIST.PRICE in the criteria section, but I need someone to push me in
the right direction.
Click to expand...
 
A

Ange Kappas

Hi Allen,
Have tried the below, but it still returns the records
from the RESPEL table which satisfy the criteria in the PRICELIST Table. In
other words, the query is solely connected to the criteria in the PRICELIST
and does not include records which have a blank field in the PRICELIST field
of the RESPEL Table.

Here is the SQL that I have at the moment:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.[DAILY CHARGE], RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL,
RESPEL.DAYS, RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE,
RESPEL.RESNO, RESPEL.RESNAME, RESPEL.STATUS, IIf([RESPEL].[PRICELIST] Is
Null,[RESPEL].[DAILY CHARGE],[PRICELIST].[PRICE]) AS Expr1
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND ((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY] And
([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));


I've experimented a bit but still cannot get the results I need.

Any more ideas, it would be much appreciated.

Thanks
Ange



----- Original Message -----
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Sunday, February 17, 2008 3:00 PM
Subject: Re: Adjusting SQL in Query

Try typing an expression like this into a blank column in the Field row,
in query design:
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE], PRICELIST.PRICE)

The SQL view would be as below. Note some other changes here as well:
a) The sub-select used the same query as in the main query.
You need to alias this (as shown.)

b) DATE is a reserved word. Hence square brackets added.

c) Several other field names are reserved words too. Although you can bet
around it in this context with the square brackets, you might want to
avoid these names when designing your tables. Examples here are Date,
Name, Start, and End. For a more comprehensive list of names to avoid,
see:
http://allenbrowne.com/AppIssueBadWord

Haven't checked all the brackets here, but this kind of thing:

SELECT RUNDATE.[DATE],
RESPEL.ROOMNO,
RESPEL.SURNAME,
RESPEL.[NAME],
RESPEL.COMPANY,
PRICELIST.PRICE,
RESPEL.ARRIVAL,
RESPEL.DAYS,
RESPEL.DEPARTURE,
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE],
PRICELIST.PRICE) AS ThePrice,
RESPEL.TYPECHARGE,
RESPEL.RESNO,
RESPEL.RESNAME,
RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST
ON RESPEL.PRICELIST = PRICELIST.CATEGORY
WHERE ((RUNDATE.[DATE] >= [PRICELIST]![START]
And (RUNDATE.[DATE]) <= [PRICELIST]![END])
AND ((RESPEL.DEPARTURE) >
(SELECT Dupe.[DATE] FROM [RUNDATE] AS Dupe))
AND ((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY]) = [PRICELIST]![COMPANY]
And ([RESPEL]![COMPANY])=[PRICELIST]![COMPANY])
AND (([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]
And ([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

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

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

Ange Kappas said:
Hi All,
I've progressed a filter in a query of mine TODAY CHARGES
to lookup up records according to the following SQL:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL, RESPEL.DAYS,
RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE, RESPEL.RESNO,
RESPEL.RESNAME, RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND
((RESPEL.STATUS)="IN") AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]
And ([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

What I want to do either in Design View or adding something to the above
SQL is:

When in the RESPEL.PRICELIST is NULL to place in the field
PRICELIST.PRICE of the Query TODAY CHARGES the value from the table
RESPEL.DAILY CHARGE.

In other words if the field PRICELIST in the Table RESPEL does not have a
value not to lookup the value from the price list as it does in the above
SQL but to take the value from the table RESPEL from the field DAILY
CHARGE.

Much Appreciated if someone can help me on this.

It's probably quite a simple inclusion in the Design View on the field
PRICELIST.PRICE in the criteria section, but I need someone to push me in
the right direction.
Click to expand...
Click to expand...
 
A

Allen Browne

That's how Nulls work, Ange. Nothing ever equals Null.

So (for example), if you set criteria of:
[RESPEL]![TYPECHARGE] = [PRICELIST]![CHARGE]
when either of those fields is Null, the record is excluded.

You can alter that by specifically including the nulls, e.g.:
(([RESPEL]![TYPECHARGE] = [PRICELIST]![CHARGE])
OR ([RESPEL]![TYPECHARGE] Is Null))

Watch the bracketing when mixing ANDs and ORs.

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

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

Ange Kappas said:
Hi Allen,
Have tried the below, but it still returns the records
from the RESPEL table which satisfy the criteria in the PRICELIST Table.
In
other words, the query is solely connected to the criteria in the
PRICELIST
and does not include records which have a blank field in the PRICELIST
field
of the RESPEL Table.

Here is the SQL that I have at the moment:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.[DAILY CHARGE], RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL,
RESPEL.DAYS, RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE,
RESPEL.RESNO, RESPEL.RESNAME, RESPEL.STATUS, IIf([RESPEL].[PRICELIST] Is
Null,[RESPEL].[DAILY CHARGE],[PRICELIST].[PRICE]) AS Expr1
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND
((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY] And
([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));


I've experimented a bit but still cannot get the results I need.

Any more ideas, it would be much appreciated.

Thanks
Ange



----- Original Message -----
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Sunday, February 17, 2008 3:00 PM
Subject: Re: Adjusting SQL in Query

Try typing an expression like this into a blank column in the Field row,
in query design:
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE], PRICELIST.PRICE)

The SQL view would be as below. Note some other changes here as well:
a) The sub-select used the same query as in the main query.
You need to alias this (as shown.)

b) DATE is a reserved word. Hence square brackets added.

c) Several other field names are reserved words too. Although you can bet
around it in this context with the square brackets, you might want to
avoid these names when designing your tables. Examples here are Date,
Name, Start, and End. For a more comprehensive list of names to avoid,
see:
http://allenbrowne.com/AppIssueBadWord

Haven't checked all the brackets here, but this kind of thing:

SELECT RUNDATE.[DATE],
RESPEL.ROOMNO,
RESPEL.SURNAME,
RESPEL.[NAME],
RESPEL.COMPANY,
PRICELIST.PRICE,
RESPEL.ARRIVAL,
RESPEL.DAYS,
RESPEL.DEPARTURE,
IIf(RESPEL.PRICELIST Is Null, RESPEL.[DAILY CHARGE],
PRICELIST.PRICE) AS ThePrice,
RESPEL.TYPECHARGE,
RESPEL.RESNO,
RESPEL.RESNAME,
RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST
ON RESPEL.PRICELIST = PRICELIST.CATEGORY
WHERE ((RUNDATE.[DATE] >= [PRICELIST]![START]
And (RUNDATE.[DATE]) <= [PRICELIST]![END])
AND ((RESPEL.DEPARTURE) >
(SELECT Dupe.[DATE] FROM [RUNDATE] AS Dupe))
AND ((RESPEL.STATUS)="IN")
AND (([RESPEL]![COMPANY]) = [PRICELIST]![COMPANY]
And ([RESPEL]![COMPANY])=[PRICELIST]![COMPANY])
AND (([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]
And ([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

Ange Kappas said:
Hi All,
I've progressed a filter in a query of mine TODAY CHARGES
to lookup up records according to the following SQL:

SELECT RUNDATE.DATE, RESPEL.ROOMNO, RESPEL.SURNAME, RESPEL.NAME,
RESPEL.COMPANY, PRICELIST.PRICE, RESPEL.ARRIVAL, RESPEL.DAYS,
RESPEL.DEPARTURE, RESPEL.PRICELIST, RESPEL.TYPECHARGE, RESPEL.RESNO,
RESPEL.RESNAME, RESPEL.STATUS
FROM RUNDATE, RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST =
PRICELIST.CATEGORY
WHERE (((RUNDATE.DATE)>=[PRICELIST]![START] And
(RUNDATE.DATE)<=[PRICELIST]![END]) AND
((RESPEL.DEPARTURE)>(SELECT[DATE]FROM[RUNDATE])) AND
((RESPEL.STATUS)="IN") AND (([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]
And ([RESPEL]![COMPANY])=[PRICELIST]![COMPANY]) AND
(([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE] And
([RESPEL]![TYPECHARGE])=[PRICELIST]![CHARGE]));

What I want to do either in Design View or adding something to the above
SQL is:

When in the RESPEL.PRICELIST is NULL to place in the field
PRICELIST.PRICE of the Query TODAY CHARGES the value from the table
RESPEL.DAILY CHARGE.

In other words if the field PRICELIST in the Table RESPEL does not have
a value not to lookup the value from the price list as it does in the
above SQL but to take the value from the table RESPEL from the field
DAILY CHARGE.

Much Appreciated if someone can help me on this.

It's probably quite a simple inclusion in the Design View on the field
PRICELIST.PRICE in the criteria section, but I need someone to push me
in the right direction.
Click to expand...
Click to expand...
Click to expand...
 

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

Date Difference In Query 2
Setting Up A Query with Certain Date Criteria 0
Code Stops Due to No Values In Query 5
Checking a Status Control from a Form Before Proceeding 0
Checking a Status Control from a Form Before Proceeding2 1

Top