CODEWITHSUNDEEP
sqlms-access
GettingStarted
GettingStarted

Reputation: 7625

How can I get data from the Form into my Query?

I want to run a query if

Forms!txtLanguageInd = "Spanish"

and run another query if its not

Here is my SQL query

IIf(Forms!HiddenUserCheck!txtLanguageInd = "Spanish", 
(SELECT UserList.Clerkid, IIf([LastName]=" Smith",[LastName] & [FirstName],[FirstName] & " " & [LastName]) AS FullName, UserList.Status, UserList.ActiveToday
FROM UserList
WHERE (((UserList.Status)="Clerk") AND ((UserList.ActiveToday)=True)) OR (((UserList.Status)="Drug Court") AND ((UserList.ActiveToday)=True)) AND SpanishPrompt=True
ORDER BY IIf([LastName]=" Smith",[LastName] & [FirstName],[LastName] & ", " & [FirstName]);),
(SELECT UserList.Clerkid, IIf([LastName]=" Smith",[LastName] & [FirstName],[FirstName] & " " & [LastName]) AS FullName, UserList.Status, UserList.ActiveToday
FROM UserList
WHERE (((UserList.Status)="Clerk") AND ((UserList.ActiveToday)=True)) OR (((UserList.Status)="Drug Court") AND ((UserList.ActiveToday)=True)) AND SpanishPrompt=False
ORDER BY IIf([LastName]=" Smith",[LastName] & [FirstName],[LastName] & ", " & [FirstName]);))

Upvotes: 1

Views: 54

Answers (2)

Andre
Andre

Reputation: 27644

You don't need this outer IIf at all, just put the condition into the WHERE clause.

This should do what you want, but you should check the order of precedence of OR and AND in the WHERE clause, you probably want another set of parentheses around the two OR conditions.

SELECT UserList.Clerkid, IIf([LastName]=" Smith",[LastName] & [FirstName],[FirstName] & " " & [LastName]) AS FullName, 
       UserList.Status, UserList.ActiveToday
FROM UserList

WHERE ((UserList.Status="Clerk") AND (UserList.ActiveToday=True)) 
   OR ((UserList.Status="Drug Court") AND (UserList.ActiveToday=True))
   AND SpanishPrompt=(Forms!HiddenUserCheck!txtLanguageInd = "Spanish")

ORDER BY IIf([LastName]=" Smith",[LastName] & [FirstName],[LastName] & ", " & [FirstName]);

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

I think your issue is substantial...

SELECT IIF(SomeVariable=1,"TrueValue","FalseValue")

cannot be used as

IIF(SomeVariable=1,(Execute one statement),(Execute another statment))

Your are mixing the concept of a conditional expression and of a conditional processing... The semicolons you've put at the end of both of your statements indicate this clearly...

And the error return more than one field points to the same. If your two statements would return just one scalar value, this might work, but - for sure! not with a full statement returning many fields...

Upvotes: 2

Related Questions