Avatar of J.R. Sitman
J.R. Sitman
Flag for United States of America asked on

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;
Microsoft Access

Avatar of undefined
Last Comment
J.R. Sitman

8/22/2022 - Mon
aikimark

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

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

prompt
example
aikimark

So, which is it,  "Office name" or "Officer name"?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
J.R. Sitman

ASKER
sorry.  officername
aikimark

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

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

name
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Helen Feddema

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

ASKER
I need the query for a form I already designed
Helen Feddema

Take a look at the controls and code on my sample form -- you might be able to modify them to work on your form.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
J.R. Sitman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
J.R. Sitman

ASKER
found my own solution outside of EE