Solved Does Access Support ANY Keyword in its Queries?

[prabha_friend]

Session: IIf("Morning"=Any (SELECT Subscription.Session FROM Subscription WHERE (((Subscription.ID) Not In (SELECT closure.[Subscription] from [Closure])));),"Morning","Evening")

 

[June7]

I never heard of it so had to look it up. I tried it and it worked. What happens when you try your expression?

 

[prabha_friend]

I am getting syntax error

 

[The_Doc_Man]

I believe the syntax error is due to this:

"Morning"=Any (SELECT

The syntax analyzer for IIF expects an expression so will parse it as such. You have a quoted string constant ("Morning") followed by an equal sign (=) so the thing that follows the equals sign will have to produce the word "Morning" in order to be true. But the thing that follows the equals sign is an unquoted word followed by a space and an opening parenthesis that is part of a longer expression. By rules of syntax, "(x..." introduces a parenthetical expression that, when resolved, will yield a value. That word "ANY" (not quoted) is treated like a value because you have nothing in that context to say otherwise. So what you have is syntactically seen as <value> <space> <value>. The syntax error normally would be "missing operator" but because it is inside a library function, the specific error is masked and the function instead returns the generic "Synax Error."

The problem is that in that context, "ANY" isn't being treated as a query element or keyword. It is in the context of an expression (because it is inside a function that expects expressions.)

 

[June7]

I tested that expression structure and it did not error but did not give result I expected.

I doubt it will for you even if syntax error is resolved. Remove semi-colon from end of the SQL. (Doc, ANY is seen as a keyword, not a value.)

Provide sample data and desired output.

 

[The_Doc_Man]

To me the question depends on whether INSIDE THE CONTEXT OF THE IIF FUNCTION, you can trigger an SQL context. If you say it works but gives you an unexpected result, the question remains - how is that criteria expression being parsed?

 

[June7]

I expect results will be incorrect because expression is not dependent on any data in the main query. Literal text input of "Morning" should probably be a field. Otherwise, same result returns for every record. I was able to build an expression that did return dynamic result.

 

[MadPiet]

Session: IIf("Morning"=Any (SELECT Subscription.Session FROM Subscription WHERE (((Subscription.ID) Not In (SELECT closure.[Subscription] from [Closure])));),"Morning","Evening")

What are you trying to do? Tell us the question you're trying to answer. Are you trying to find all subscriptions without a Closure record?
SELECT ... FROM Subscription s LEFT JOIN Closure c ON s.SubscriptionID = c.ClosureID WHERE c.ClosureID IS NULL?
or likely easier...
SELECT ... FROM Subscription s WHERE NOT EXISTS (SELECT 1 FROM Closure c WHERE c.SubscriptionDI = s.ClosureID)

 

[MadPiet]

Transformed to equivalent statement without ANY.

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  NOT EXISTS (SELECT e2.sal
                   FROM emp e2
                   WHERE e2.deptno = 20
                   AND   e1.sal <= e2.sal);

 

[prabha_friend]

What are you trying to do? Tell us the question you're trying to answer. Are you trying to find all subscriptions without a Closure record?
SELECT ... FROM Subscription s LEFT JOIN Closure c ON s.SubscriptionID = c.ClosureID WHERE c.ClosureID IS NULL?
or likely easier...
SELECT ... FROM Subscription s WHERE NOT EXISTS (SELECT 1 FROM Closure c WHERE c.SubscriptionDI = s.ClosureID)

Customers can request for items. So if the customer is already a subscriber and the same subscriber already having a subscription in morning, we just have to deliver ASAP (Morning) but if he has subscriptions only for Evening then no other way. we can deliver only on "Evening".

Session (Morning)		Morning
Session (Morning and Evening)		Morning
Session (Only Evening)		Evening

 

[prabha_friend]

 

[prabha_friend]

I believe the syntax error is due to this:

"Morning"=Any (SELECT

The syntax analyzer for IIF expects an expression so will parse it as such. You have a quoted string constant ("Morning") followed by an equal sign (=) so the thing that follows the equals sign will have to produce the word "Morning" in order to be true. But the thing that follows the equals sign is an unquoted word followed by a space and an opening parenthesis that is part of a longer expression. By rules of syntax, "(x..." introduces a parenthetical expression that, when resolved, will yield a value. That word "ANY" (not quoted) is treated like a value because you have nothing in that context to say otherwise. So what you have is syntactically seen as <value> <space> <value>. The syntax error normally would be "missing operator" but because it is inside a library function, the specific error is masked and the function instead returns the generic "Synax Error."

The problem is that in that context, "ANY" isn't being treated as a query element or keyword. It is in the context of an expression (because it is inside a function that expects expressions.)

'ANY'way to make it work Doc?

 

[The_Doc_Man]

Look at MadPiet's suggestion. Or create a separate low-level query that attempts to COUNT the number of records that have "Morning" in the critical places and then in a higher query, DLookup that count to see if it is zero or not zero. I think it is the IIF function that is confusing the issue.

 

[cheekybuddha]

How about more simply:

SELECT
  s.Customer,
  Nz(s.Session, 'Morning') AS [When]
FROM Subscription s
;

 

[prabha_friend]

How about more simply:

SELECT
  s.Customer,
  Nz(s.Session, 'Morning') AS [When]
FROM Subscription s
;

Problem solved but 'ANY' is still there. So can we conclude ANY cannot be used in access queries?

 

[cheekybuddha]

I don't *think* that Access supports ANY (but maybe it does?), but the point is do you actually need to use ANY in the first place?

 

[prabha_friend]

I may use if I someone concludes its availability in ms access queries... who knows? which weapon is needed in which fights...

 

[The_Doc_Man]

A lot of references online do not explicitly mention "ANY", "ALL", and "SOME" as valid for Access SQL, but many reference talk about general SQL and point out that you can sometimes get the same effect using other keywords. For instance, "ANY" can be handled by using the "IN" keyword, and the "SOME" option can be managed with "EXISTS" - though they are not exactly equivalent. Access SQL keyword lists DO include ANY, ALL, and SOME, so I would suspect they work. The W3 Schools web pages reference ANY, ALL, and SOME as operators used to represent aggregates of records being searched for particular matches.

The SQL 1999 reference calls ANY and SOME "quantified comparison" operators in the same general family as AVG, MIN, MAX, SUM, EVERY, & COUNT. It refers to keyword ALL as a "set quantifier" (along with DISTINCT).

The SQL 1999 reference explicitly defines that "X IN Y" and "X = ANY Y" are semantically equivalent, so that is a substitution that might help, where in this context, X is a single value and Y can be a sub-query.

 

[June7]

Syntax error caused by semi-colon in expression. See post 5.