Re: Adjusting SQL in Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <AllenBrowne@xxxxxxxxxxxxxx>
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ange Kappas" <angekap@xxxxxx> wrote in message
news:fp95p3$dqi$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: Date Difference In Query
    ... Departure in both the tables you are using in the query. ... , [RESPEL ALL CHARGES].BASIS ... FROM [RESPEL ALL CHARGES] INNER JOIN RESERVATIONS ON [RESPEL ALL ...
    (microsoft.public.access.queries)
  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)