Null value in multi-parameter queries

[Guest]

I am running the following query with three parameters that I enter from
combo boxes in a form:
SELECT tblPROJECTS.Region, tblPROJECTS.Status, tblSUBJECT.SubjectName,
tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.Budget
FROM tblSUBJECT INNER JOIN (tblPROJECTS INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) ON
tblSUBJECT.SubjectName = tblProjectSubject.SubjectName
WHERE (((tblPROJECTS.Region)=[Forms]![frmParameters]![Region]) AND
((tblPROJECTS.Status)=[Forms]![frmParameters]![Status]) AND
((tblSUBJECT.SubjectName)=[Forms]![frmParameters]![Subject]));
It works fine as long as all three parameters (region, status and subject)
have a value.
However, I want to be able to leave one, two or all three parameters without
a value and the query would return all the records for the category(ies) that
have null values. Is that possible?
Thanks in advance.
Niels

 

[Guest]

Try this

SELECT tblPROJECTS.Region, tblPROJECTS.Status, tblSUBJECT.SubjectName,
tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.Budget
FROM tblSUBJECT INNER JOIN (tblPROJECTS INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) ON
tblSUBJECT.SubjectName = tblProjectSubject.SubjectName
WHERE tblPROJECTS.Region Like NZ([Forms]![frmParameters]![Region],"*") AND
tblPROJECTS.Status Like Nz([Forms]![frmParameters]![Status],"*") AND
tblSUBJECT.SubjectName Like Nz([Forms]![frmParameters]![Subject],"*")

Using Like with NZ to return * if the value null, which mean all records

 

[Allen Browne]

You can set up the WHERE clause so it returns the value TRUE if the text box
is left Null, or else tests for a match if the text box contains a value:

WHERE ((([Forms]![frmParameters]![Region] Is Null)
OR (tblPROJECTS.Region = [Forms]![frmParameters]![Region]))
AND (([Forms]![frmParameters]![Status] Is Null)
OR (tblPROJECTS.Status = [Forms]![frmParameters]![Status]))
AND (([Forms]![frmParameters]![Subject] Is Null)
OR (tblSUBJECT.SubjectName = [Forms]![frmParameters]![Subject])));

It might be more efficient to design the database so it does not perform all
these comparisions though. For example, if this is the source for a report,
you could leave the query without any WHERE clause, and build a
WhereConditon string to limit the report. Typically you put a command button
on your form, and build the WhereCondition from the non-blank boxes.

For an example, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

 

[Guest]

Many thanks for this Allen.

I entered your query in the SQL section of a new query, and it worked
perfectly.

I am still a beginner, have yet to attend the VBA course.

Unfortunately, when I looked at the SQL after the query ran, the coding
entered went from your suggested (tailored obviously to my info):

SELECT Tbl_PropertyDBMain.[Property name], Tbl_PropertyDBMain.CityArea,
Tbl_PropertyDBMain.City, Tbl_PropertyDBMain.Region,
Tbl_PropertyDBMain.PropertyID, Tbl_PropertyDBMain.ErfSize,
Tbl_PropertyDBMain.GLA, Tbl_PropertyDBMain.Lessee,
Tbl_PropertyDBMain.[Purchase price]
FROM Tbl_PropertyDBMain
WHERE ((([Forms]![*Rob_Basis for area search]![City area] Is Null)
OR (Tbl_PropertyDBMain.CityArea=[Forms]![*Rob_Basis for area search]![City
area]))
AND (([Forms]![*Rob_Basis for area search]![City] Is Null)
OR (Tbl_PropertyDBMain.City=[Forms]![*Rob_Basis for area search]![City]))
AND (([Forms]![*Rob_Basis for area search]![Region] Is Null)
OR (Tbl_PropertyDBMain.Region=[Forms]![*Rob_Basis for area
search]![Region])));

to this:

SELECT Tbl_PropertyDBMain.[Property name], Tbl_PropertyDBMain.CityArea,
Tbl_PropertyDBMain.City, Tbl_PropertyDBMain.Region,
Tbl_PropertyDBMain.PropertyID, Tbl_PropertyDBMain.ErfSize,
Tbl_PropertyDBMain.GLA, Tbl_PropertyDBMain.Lessee,
Tbl_PropertyDBMain.RegDate, Tbl_PropertyDBMain.[Purchase price]
FROM Tbl_PropertyDBMain
WHERE ((([Forms]![*Rob_Basis for area search]![City area]) Is Null)
AND (([Forms]![*Rob_Basis for area search]![City]) Is Null)
AND (([Forms]![*Rob_Basis for area search]![Region]) Is Null))
OR (((Tbl_PropertyDBMain.CityArea)=[Forms]![*Rob_Basis for area
search]![City area])
AND (([Forms]![*Rob_Basis for area search]![City]) Is Null)
AND (([Forms]![*Rob_Basis for area search]![Region]) Is Null))
OR (((Tbl_PropertyDBMain.City)=[Forms]![*Rob_Basis for area search]![City])
AND (([Forms]![*Rob_Basis for area search]![City area]) Is Null)
AND (([Forms]![*Rob_Basis for area search]![Region]) Is Null))
OR (((Tbl_PropertyDBMain.CityArea)=[Forms]![*Rob_Basis for area
search]![City area])
AND ((Tbl_PropertyDBMain.City)=[Forms]![*Rob_Basis for area search]![City])
AND (([Forms]![*Rob_Basis for area search]![Region]) Is Null))
OR (((Tbl_PropertyDBMain.Region)=[Forms]![*Rob_Basis for area
search]![Region])
AND (([Forms]![*Rob_Basis for area search]![City area]) Is Null)
AND (([Forms]![*Rob_Basis for area search]![City]) Is Null))
OR (((Tbl_PropertyDBMain.CityArea)=[Forms]![*Rob_Basis for area
search]![City area])
AND ((Tbl_PropertyDBMain.Region)=[Forms]![*Rob_Basis for area
search]![Region])
AND (([Forms]![*Rob_Basis for area search]![City]) Is Null))
OR (((Tbl_PropertyDBMain.City)=[Forms]![*Rob_Basis for area search]![City])
AND ((Tbl_PropertyDBMain.Region)=[Forms]![*Rob_Basis for area
search]![Region])
AND (([Forms]![*Rob_Basis for area search]![City area]) Is Null))
OR (((Tbl_PropertyDBMain.CityArea)=[Forms]![*Rob_Basis for area
search]![City area])
AND ((Tbl_PropertyDBMain.City)=[Forms]![*Rob_Basis for area search]![City])
AND ((Tbl_PropertyDBMain.Region)=[Forms]![*Rob_Basis for area
search]![Region]));

Why all the extra info? Any reason for this / something I should have
entered? If I want to make changes this could be a pain - is this simply
standard?

Many thanks
Will

You can set up the WHERE clause so it returns the value TRUE if the text box
is left Null, or else tests for a match if the text box contains a value:

WHERE ((([Forms]![frmParameters]![Region] Is Null)
OR (tblPROJECTS.Region = [Forms]![frmParameters]![Region]))
AND (([Forms]![frmParameters]![Status] Is Null)
OR (tblPROJECTS.Status = [Forms]![frmParameters]![Status]))
AND (([Forms]![frmParameters]![Subject] Is Null)
OR (tblSUBJECT.SubjectName = [Forms]![frmParameters]![Subject])));

It might be more efficient to design the database so it does not perform all
these comparisions though. For example, if this is the source for a report,
you could leave the query without any WHERE clause, and build a
WhereConditon string to limit the report. Typically you put a command button
on your form, and build the WhereCondition from the non-blank boxes.

For an example, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

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

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

I am running the following query with three parameters that I enter from
combo boxes in a form:
SELECT tblPROJECTS.Region, tblPROJECTS.Status, tblSUBJECT.SubjectName,
tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.Budget
FROM tblSUBJECT INNER JOIN (tblPROJECTS INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) ON
tblSUBJECT.SubjectName = tblProjectSubject.SubjectName
WHERE (((tblPROJECTS.Region)=[Forms]![frmParameters]![Region]) AND
((tblPROJECTS.Status)=[Forms]![frmParameters]![Status]) AND
((tblSUBJECT.SubjectName)=[Forms]![frmParameters]![Subject]));
It works fine as long as all three parameters (region, status and subject)
have a value.
However, I want to be able to leave one, two or all three parameters
without
a value and the query would return all the records for the category(ies)
that
have null values. Is that possible?
Thanks in advance.
Niels

 

[GBAccess]

Most helpful...thank you

Try this

SELECT tblPROJECTS.Region, tblPROJECTS.Status, tblSUBJECT.SubjectName,
tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.Budget
FROM tblSUBJECT INNER JOIN (tblPROJECTS INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) ON
tblSUBJECT.SubjectName = tblProjectSubject.SubjectName
WHERE tblPROJECTS.Region Like NZ([Forms]![frmParameters]![Region],"*") AND
tblPROJECTS.Status Like Nz([Forms]![frmParameters]![Status],"*") AND
tblSUBJECT.SubjectName Like Nz([Forms]![frmParameters]![Subject],"*")

Using Like with NZ to return * if the value null, which mean all records

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

I am running the following query with three parameters that I enter from
combo boxes in a form:
SELECT tblPROJECTS.Region, tblPROJECTS.Status, tblSUBJECT.SubjectName,
tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.Budget
FROM tblSUBJECT INNER JOIN (tblPROJECTS INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) ON
tblSUBJECT.SubjectName = tblProjectSubject.SubjectName
WHERE (((tblPROJECTS.Region)=[Forms]![frmParameters]![Region]) AND
((tblPROJECTS.Status)=[Forms]![frmParameters]![Status]) AND
((tblSUBJECT.SubjectName)=[Forms]![frmParameters]![Subject]));
It works fine as long as all three parameters (region, status and subject)
have a value.
However, I want to be able to leave one, two or all three parameters without
a value and the query would return all the records for the category(ies) that
have null values. Is that possible?
Thanks in advance.
Niels