Microsoft Access
Microsoft Access

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: 2017-06-30

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 48

aikimarkCommented: 2017-06-30

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: 2017-06-30

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

LVL 48

aikimarkCommented: 2017-06-30

So, which is it, "Office name" or "Officer name"?

J.R. SitmanIT DirectorAuthor Commented: 2017-06-30

sorry. officername

LVL 48

aikimarkCommented: 2017-06-30

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: 2017-06-30

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

LVL 31

Helen FeddemaCommented: 2017-06-30

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:

accarch129.zip

J.R. SitmanIT DirectorAuthor Commented: 2017-06-30

I need the query for a form I already designed

LVL 31

Helen FeddemaCommented: 2017-07-02

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: 2017-07-02

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