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?
 
hnasrCommented:
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
 
Ryan ChongCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
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
 
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
 
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
 
hnasrCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.