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!
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.PostSiezure955D
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerN
ORDER BY Complaints.ComplaintNo;
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955D
FROM (qryOfficerName INNER JOIN Officers ON qryOfficerName.ContactID = Officers.ContactID) INNER JOIN Complaints ON Officers.ContactID = Complaints.OfficerID
WHERE (((qryOfficerName.OfficerN
ORDER BY Complaints.ComplaintNo;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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
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
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
accarch129.zip
Take a look at the controls and code on my sample form -- you might be able to modify them to work on your form.
I go the answer from another programmer I know. see below.
Query1:
PARAMETERS [Officer Last Name:] text;
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955D
Complaints.TargetDate, Complaints.Stats2 FROM ( qryOfficerNamePickName INNER JOIN Officers ON qryOfficerNamePickName.Con
Complaints.Stats2 Not In
("closed") ORDER BY Complaints.ComplaintNo;
qryOfficerNamePickName:
PARAMETERS [Officer Last Name:] Text ( 255 ); SELECT CONtblPeople.ContactID, IIf(IsNull([LastName]),"N/
[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/
Query1:
PARAMETERS [Officer Last Name:] text;
SELECT Complaints.ComplaintNo, Complaints.CaseName, Complaints.PostSiezure955D
Complaints.TargetDate, Complaints.Stats2 FROM ( qryOfficerNamePickName INNER JOIN Officers ON qryOfficerNamePickName.Con
Complaints.Stats2 Not In
("closed") ORDER BY Complaints.ComplaintNo;
qryOfficerNamePickName:
PARAMETERS [Officer Last Name:] Text ( 255 ); SELECT CONtblPeople.ContactID, IIf(IsNull([LastName]),"N/
[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/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial