Microsoft query help

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;
J.R. SitmanIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
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 DirectorAuthor Commented:
It prompts me for a "missing officer name"  I want it to prompt for the officer name.  See attached

prompt
example
aikimarkCommented:
So, which is it,  "Office name" or "Officer name"?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
J.R. SitmanIT DirectorAuthor Commented:
sorry.  officername
aikimarkCommented: 
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 DirectorAuthor Commented:
It's giving me all the officers, not just the name I enter.

name
Helen FeddemaCommented:
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 DirectorAuthor Commented:
I need the query for a form I already designed
Helen FeddemaCommented:
Take a look at the controls and code on my sample form -- you might be able to modify them to work on your form.
J.R. SitmanIT DirectorAuthor 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]);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J.R. SitmanIT DirectorAuthor Commented:
found my own solution outside of EE
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.