Microsoft query help

J.R. Sitman
J.R. Sitman used Ask the Experts™
on
I need this query to have officename is is not null and prompt the user for the officename.  The prompt is what I don't know how to do since there is already "is not" in the criteria.

SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, qryOfficerName.OfficerName, Complaints.TargetDate, Complaints.Stats2
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerName) Is Not Null) AND ((Complaints.Stats2) Not In ("closed")))
ORDER BY Complaints.ComplaintNo;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
If officename is not null will be in your query, the only rows will have a non-null officename value.

You might need a union query, where the first query in the list contains non-null officename rows and the second query would contain a prompted value for the officename.
Example:
SELECT Officename, Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, qryOfficerName.OfficerName, Complaints.TargetDate, Complaints.Stats2
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerName) Is Not Null) AND ((Complaints.Stats2) Not In ("closed")))
And Officename Is Not Null
UNION ALL
SELECT [Enter Missing Office Name], Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, qryOfficerName.OfficerName, Complaints.TargetDate, Complaints.Stats2
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerName) Is Not Null) AND ((Complaints.Stats2) Not In ("closed")))
And Officename Is Null
ORDER BY Complaints.ComplaintNo

Open in new window

J.R. SitmanIT Director

Author

Commented:
It prompts me for a "missing officer name"  I want it to prompt for the officer name.  See attached

prompt
example
Top Expert 2014

Commented:
So, which is it,  "Office name" or "Officer name"?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

J.R. SitmanIT Director

Author

Commented:
sorry.  officername
Top Expert 2014

Commented:
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, 
qryOfficerName.OfficerName, Complaints.TargetDate, Complaints.Stats2
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) 
INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerName) Is Not Null) AND ((Complaints.Stats2) Not In ("closed")))
UNION ALL
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, 
[Enter Missing Office Name], Complaints.TargetDate, Complaints.Stats2
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) 
INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerName) Is Null) AND ((Complaints.Stats2) Not In ("closed")))
ORDER BY Complaints.ComplaintNo

Open in new window

J.R. SitmanIT Director

Author

Commented:
It's giving me all the officers, not just the name I enter.

name
Top Expert 2009

Commented:
If you want just results for a certain Officer Name, it might be better to do this via a form with a combo box for selecting that name (or better, the key ID field of that record), and use it to return filtered results.  See my Fancy Filters sample database (attached).  Here is a screen shot of the form:
Fancy Filters formaccarch129.zip
J.R. SitmanIT Director

Author

Commented:
I need the query for a form I already designed
Top Expert 2009

Commented:
Take a look at the controls and code on my sample form -- you might be able to modify them to work on your form.
IT Director
Commented:
I go the answer from another programmer I know.  see below.

Query1:

PARAMETERS  [Officer Last Name:] text;
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955Date, qryOfficerNamePickName.OfficerName,
Complaints.TargetDate, Complaints.Stats2 FROM ( qryOfficerNamePickName INNER JOIN Officers ON qryOfficerNamePickName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID WHERE
Complaints.Stats2 Not In
("closed")  ORDER BY Complaints.ComplaintNo;


qryOfficerNamePickName:

PARAMETERS [Officer Last Name:] Text ( 255 ); SELECT CONtblPeople.ContactID, IIf(IsNull([LastName]),"N/A",[LastName] & ", " & [Title] & " " &
[FirstName]) AS OfficerName, Officers.OfficerCode, Officers.Rank FROM CONtblPeople INNER JOIN Officers ON CONtblPeople.ContactID = Officers.ContactID WHERE LastName LIKE [Officer Last Name:] ORDER BY IIf(IsNull([LastName]),"N/A",[LastName] & ", " & [Title] & " " & [FirstName]);
J.R. SitmanIT Director

Author

Commented:
found my own solution outside of EE

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial