Hi Fred. Thanks for your response. I may have been a little unclear in my
original post.
There isnt a value called "Any", this is just an option a user can select on
a form before generating a report.
The idea is that the first 4 options actually exist in the database, and the
last option called "any" should retrieve all of the records. The code is
actually a condition inside a query. Pasted below (Sorry its a bit big):
SELECT Employees.EmployeeID, [Firstname] & " " & [Lastname] AS Name,
[Description] & " : " & [Classname] & " : " & [SubClass] AS [Training
Description], Training.Number, Training.Expiration,
Employees.CurrentEmployee, SubClass.RiskLevel
FROM (Employees INNER JOIN Training ON Employees.EmployeeID =
Training.Employee) INNER JOIN ((Certificates INNER JOIN Classes ON
Certificates.CertificateID = Classes.Classparent) INNER JOIN SubClass ON
Classes.ClassID = SubClass.SubClassParent) ON Training.Certificate =
SubClass.SubClassID
WHERE (((Employees.CurrentEmployee)=IIf([Forms]![Build Certificate
Report]![FrameEmployee]=1,-1,IIf([Forms]![Build Certificate
Report]![FrameEmployee]=2,0,(Employees.CurrentEmployee) Like "*"))) AND
((SubClass.RiskLevel)=IIf([Forms]![Build Certificate
Report]![FrameRisk]=1,"Low",IIf([Forms]![Build Certificate
Report]![FrameRisk]=2,"Medium",IIf([Forms]![Build Certificate
Report]![FrameRisk]=3,"High",IIf([Forms]![Build Certificate
Report]![FrameRisk]=4,"N/A",""))))));
fredg said:
I'm having some trouble with my query. A user has 5 options to choose from,
Low, Medium, High, N/A, Any. This information is to be used in a customised
report.
My query below is able to find the first 4 options but i'm at a loss as to
find the fifth option "Any". THis option needs to find any record that
matches either Low, Medium, High, or N/A.
I've tried "Is Not Null" and "or" statements, a * wildcard. None of which
work. Help?
IIf([Forms]![Build Certificate
Report]![FrameRisk]=1,"Low",IIf([Forms]![Build Certificate
Report]![FrameRisk]=2,"Medium",IIf([Forms]![Build Certificate
Report]![FrameRisk]=3,"High",IIf([Forms]![Build Certificate
Report]![FrameRisk]=4,"N/A",""))))
Cheers in advance.
If it's none of the first 4 than it must be "Any".
IIf([Forms]![Build Certificate
Report]![FrameRisk]=1,"Low",IIf([Forms]![Build Certificate
Report]![FrameRisk]=2,"Medium",IIf([Forms]![Build Certificate
Report]![FrameRisk]=3,"High",IIf([Forms]![Build Certificate
Report]![FrameRisk]=4,"N/A","Any"))))
A better way is to have a a lookup table with the 5 choices.
It's easier to change the value or add additional values without any
re-coding.
=DLookUp("[WordText]","TableName',"[NumberValue] = " & [Forms]![Build
Certificate Report]![FrameRisk])
If this code is on the form named [Build Certificate Report] you can
replace Forms![Build Certificate Report] ![FrameRisk] with
Me![FrameRisk]
