Re: Adjusting SQL in Query

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
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:fpbpng$jpj$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
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]));

"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: 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: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (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)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)