access exclude value IIF criteria

I have a form "Temp" with a text box called "txtTemp".  I have a table called "Temp".  I made a query called "qryTemp" which I am setting up with criteria from the Temp text box to filter the query.

My goal is when:
txtTemp = 1 -- show all the records
txtTemp = 2 -- show all records but not where the Status field = 6.

I've messed around with IIF and Like IIF in the criteria section of the query.  in the criteria section on the Status field - I have:

Like IIf([Forms]![temp]![txtTemp]=1,"*",Not 6)

So when I enter in 1 in the txtbox - all the records show in the query - but when I change the value in the txtbox to another value say 2 - it does not see the Not 6 and returns no records. ( I want all records except where Status = 6)

So I'm stuck trying how to exclude a value in the false part of an IIF statement in the criteria.

Thanks for any help with this.
john madiganAsked:
Who is Participating?

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

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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try write your query such as:

SELECT Temp.*
FROM Temp
WHERE IIf([Forms]![temp]![txtTemp]=1,1, IIF(Status = 6, 0, 1) ) = 1;

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please use this -

SELECT *
FROM yourtableName
WHERE ( 1 = IIf([Forms]![temp]![txtTemp]=1,1,0) ) OR ( Statu <> IIf([Forms]![temp]![txtTemp]=2,6,-999) )

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in addition, if the values entered in textbox: txtTemp is always 1 or 2, then try to make it as a Combobox with fixed value (set Limit to List property to Yes), or a set of radio buttons so that only one of the values from 1 or 2 can be selected.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Pawan KumarDatabase ExpertCommented:
Updated column name

SELECT *
FROM yourtableName
WHERE ( 1 = IIf([Forms]![temp]![txtTemp]=1,1,0) ) OR ( Status <> IIf([Forms]![temp]![txtTemp]=2,6,-999) )
0
Gustav BrockCIOCommented:
You can directly transfer your wording of the critera to code:

SELECT *
FROM Temp
WHERE ([Forms]![temp]![txtTemp] = 1) Or ([Forms]![temp]![txtTemp] = 2 And [Status] <> 6);

Open in new window

/gustav
0
aikimarkCommented:
This might be simpler to maintain and should perform better.
SELECT *
FROM yourtableName
WHERE [Forms]![temp]![txtTemp]=1
UNION ALL
SELECT *
FROM yourtableName
WHERE If([Forms]![temp]![txtTemp]=2 AND Status <> 6

Open in new window

0
Hamed NasrRetired IT ProfessionalCommented:
Try:
WHERE (txtTemp = 1) OR (txtTemp = 2 AND Status <> 6)

Open in new window

Use the Full reference of the Control name., as from your comment, [Forms]![temp]![txtTemp]
You may need to use the same naming Option to Status field.
0

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
john madiganAuthor Commented:
Thanks for your help.

I used the code and it worked - I just went to the SQL view of the Query - entered the code - much better than tying use the criteria field in the design view.

Thanks again.
0
Hamed NasrRetired IT ProfessionalCommented:
I used the code and it worked -
Thank you for the grading.
Please allow me to inquire on how you opted to label this thread as Low priority.

Great ideas appear simple once made public.
0
Gustav BrockCIOCommented:
Hmm .. the accepted answer was a direct copy of my solution.

/gustav
0
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.