Null value in multi-parameter queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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

Allen Browne said:
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.

NielsE said:
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
Click to expand...
Click to expand...
 
Most helpful...thank you

Ofer said:
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


NielsE said:
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
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

Query returning same field value from two diffenrent tables 1
Only one value wanted from a column with duplicates 3
Hide duplicate values in a report based on a query 2
Create Report Using Form Parameters 2
Null Values 5
Parameter queries and null values 2
query is asking for parameter value 1
Paramter Query for Null Value (Leo) 1

Back
Top