UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Using Iif In Query Criteria, Any Version    
 
   
ordnance1
post Jan 14 2020, 12:35 PM
Post#1



Posts: 727
Joined: 7-May 11



If I use the query criteria below I get no results. If I use <6 I get 7 results. What would be the proper way to write this?

CODE
=IIf([forms]![frm_Navigation]![opt_View]=5,<6,6)
Go to the top of the page
 
theDBguy
post Jan 14 2020, 12:39 PM
Post#2


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Hi. Why do you have an equal sign before the IIf() statement? Where is this criteria placed? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jan 14 2020, 12:47 PM
Post#3



Posts: 727
Joined: 7-May 11



Here is a screenshot of the query.


Attached File  Untitled_picture.png ( 52.58K )Number of downloads: 7


Go to the top of the page
 
theDBguy
post Jan 14 2020, 12:58 PM
Post#4


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks. So, yes, try it this way.

CODE
IIf([forms![frm_Navigation].[opt_View]=5,<6,6)


Let us know if it doesn't work.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jan 14 2020, 01:09 PM
Post#5



Posts: 727
Joined: 7-May 11



Thanks but I get the same result, no returned records.
Go to the top of the page
 
theDBguy
post Jan 14 2020, 01:14 PM
Post#6


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Okay, take out the criteria and enter the following as a new column. What do you get? Does the query result makes sense?

[forms![frm_Navigation].[opt_View]

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jan 14 2020, 01:32 PM
Post#7



Posts: 727
Joined: 7-May 11



Returns an error. I also tried using a TempVar and verified its value in the immediate window but it also returned no results.

CODE
IIf([TempVars]![View]=5,<6,6)


Attached File  Untitled_picture.png ( 54.19K )Number of downloads: 4
Go to the top of the page
 
ordnance1
post Jan 14 2020, 01:36 PM
Post#8



Posts: 727
Joined: 7-May 11



If I use:

CODE
=IIf([TempVars]![View]=5,2,6)


it works so the issue is clearly with the <
Go to the top of the page
 
theDBguy
post Jan 14 2020, 01:50 PM
Post#9


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Hi. It returned an error because you had a typo in the new column (missing closing bracket ]).

If you take out the equal sign at the beginning of the working criteria, does it still work?

CODE
IIf([TempVars]![View]=5,2,6)


I don't think it's necessarily the < itself. I think it's more like your syntax, where you place the <.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jan 14 2020, 01:51 PM
Post#10



Posts: 727
Joined: 7-May 11



Yes
Go to the top of the page
 
theDBguy
post Jan 14 2020, 01:52 PM
Post#11


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Hi. See my edit above. I don't think the problem is with the use of < alone. I think it's more where you put it. Anyway, there are other ways to get what you want without using IIf() or with using it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jan 14 2020, 01:54 PM
Post#12



Posts: 727
Joined: 7-May 11



With the bracket added this is the result.

Attached File  Untitled_picture.png ( 54.19K )Number of downloads: 3
Go to the top of the page
 
ordnance1
post Jan 14 2020, 02:00 PM
Post#13



Posts: 727
Joined: 7-May 11



Using returns nothing. Not sure where to head from here but appreciate the help you have provided.

CODE
IIf([forms]![frm_Navigation].[opt_View]=5,<6,6)
Go to the top of the page
 
theDBguy
post Jan 14 2020, 02:07 PM
Post#14


UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo


Hi. I personally avoid using IIf() statements as a criteria. What do you get with this one?

CODE
([forms]![frm_Navigation].[opt_View]=5 AND [Date_AgentCompleted] Is Null) OR ([forms]![frm_Navigation].[opt_View]<>5 AND [Date_AgentCompleted] Is Not Null)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
fkegley
post Jan 14 2020, 02:09 PM
Post#15


UtterAccess VIP
Posts: 23,779
Joined: 13-January 05
From: Mississippi


IIf([forms]![frm_Navigation].[opt_View]=5,<6,6)

I am not sure I understand, you want it to put a number <6, if opt_View =5, or 6 if it isn't?

That does not make any sense to me.

Are you trying to put a quoted string value, like this:?

IIf([forms]![frm_Navigation].[opt_View]=5,"<6","6")

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 
ordnance1
post Jan 14 2020, 02:21 PM
Post#16



Posts: 727
Joined: 7-May 11



opt_View can range from 1 to 6. When opt_View = 5 I need the query to return all records that have a Status between 1 and 5.

I have tried both:

IIf([forms]![frm_Navigation].[opt_View]=5,<6,6) which returns no results and

IIf([forms]![frm_Navigation].[opt_View]=5,"<6",6) which returns an error "This expression is typed incorrectly .....)
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 02:24 PM
Post#17


UtterAccess Moderator
Posts: 12,264
Joined: 6-December 03
From: Telegraph Hill


Please post the SQL view of your query, omitting your IIf() criteria.

--------------------


Regards,

David Marten
Go to the top of the page
 
ordnance1
post Jan 14 2020, 02:32 PM
Post#18



Posts: 727
Joined: 7-May 11



Here is the SQL

CODE
SELECT DISTINCT dbo_Group.Group_ID, IIf(Nz([dbo_Profit_Loss].[Agent_ID],0)>0,"P & L","         ") & " " & [GroupCategory] & " " & [ClubNumber] & " - " & [City] & ", " & [Abbreviation] & "    " & [Name] AS GroupName, dbo_Address.Agent_ID, dbo_Address.City, dbo_GroupCategory.GroupCategory_ID, dbo_Group.ClubNumber, IIf([Date_AgentCompleted] Is Not Null,6,IIf([Date_CharterRevoked] Is Not Null,5,IIf([Date_CharterReturned] Is Not Null,4,IIf([Date_Requested_CharterReturn] Is Not Null,3,IIf([Date_CharterSuspended] Is Not Null,2,IIf([Date_AgentAssigned] Is Not Null,1,0)))))) AS Status
FROM (dbo_State RIGHT JOIN ((dbo_Group INNER JOIN dbo_GroupCategory ON dbo_Group.GroupCategory_ID = dbo_GroupCategory.GroupCategory_ID) LEFT JOIN dbo_Address ON dbo_Group.Group_ID = dbo_Address.Group_ID) ON dbo_State.State_ID = dbo_Address.State_ID) LEFT JOIN dbo_Profit_Loss ON dbo_Group.Group_ID = dbo_Profit_Loss.Group_ID
WHERE (((dbo_Address.Agent_ID)=[TempVars]![Agent_ID]))
ORDER BY dbo_Address.City, dbo_GroupCategory.GroupCategory_ID;
Go to the top of the page
 
June7
post Jan 14 2020, 02:38 PM
Post#19



Posts: 1,220
Joined: 25-January 16
From: The Great Land


Equality operators (=, <, >, <=, >=, <>) cannot be dynamic. It's either used or it isn't. What you had was literal text "<" only without quote marks, therefore criteria had no operator at all.

Must use 1 operator for each parameter expression. The parameter can be dynamic but not the operator. Example:

SELECT * FROM tablename WHERE fieldname < IIf([forms]![frm_Navigation].[opt_View]=5, 6, 7)

However, that is not the result you want. Depending on selected option, you want conditional criteria - either <6 or =6.

I never use dynamic parameterized query. I prefer VBA to build criteria and apply to form or report. http://allenbrowne.com/ser-62.html

This post has been edited by June7: Jan 14 2020, 02:48 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
kfield7
post Jan 14 2020, 02:43 PM
Post#20



Posts: 1,038
Joined: 12-November 03
From: Iowa Lot


re: opt_View can range from 1 to 6. When opt_View = 5 I need the query to return all records that have a Status between 1 and 5.

Try this for WHERE:
([TempVars]![View]=5 AND[status]<6) OR ([TempVars]![View]<>5 AND [status]=6)

This can be created in the QBE view:
In the QBE, under [View] criteria put 5, then the next line put <>5. Under [Status] same criteria line as View=5 put <6, then the next line put 6.

You already have a critieria for AgentID. You'll need to put same criteria for each of the two QBE criteria lines above.
This post has been edited by kfield7: Jan 14 2020, 02:45 PM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 08:05 AM