IF Statement for Form Record Source

I want a forms record source to change depending on the value of a option button.     When I try to look at in Design view I get an "Invalid SQL statement; expected 'Delete', Insert, Procedure, select or update" error.  Will Access be able to handle this?   Or is this a syntax error?


If [Forms]![Item_Master]![OptionActive]=-1 Then
SELECT tbl_Item.ID, tbl_Item.PLU, tbl_Item.Description, tbl_Item.Size, tbl_Item.Main_Link, tbl_Item.CatSub, tbl_Item.Active, tbl_Item.Item_Added, tbl_Cat_Sub.CatSub_Name, tbl_Item.WES, tbl_Item.CAP, tbl_Item.PAC, tbl_Item.HMB, tbl_Item.EVG, tbl_Item.AllStoreMargin, tbl_Item.AdType, tbl_Item.Notes
FROM tbl_Cat_Sub RIGHT JOIN tbl_Item ON tbl_Cat_Sub.[CatSub] = tbl_Item.[CatSub];
Where [Active]=-1
Else
If [Forms]![Item_Master]![OptionActive]=0 Then
SELECT tbl_Item.ID, tbl_Item.PLU, tbl_Item.Description, tbl_Item.Size, tbl_Item.Main_Link, tbl_Item.CatSub, tbl_Item.Active, tbl_Item.Item_Added, tbl_Cat_Sub.CatSub_Name, tbl_Item.WES, tbl_Item.CAP, tbl_Item.PAC, tbl_Item.HMB, tbl_Item.EVG, tbl_Item.AllStoreMargin, tbl_Item.AdType, tbl_Item.Notes
FROM tbl_Cat_Sub RIGHT JOIN tbl_Item ON tbl_Cat_Sub.[CatSub] = tbl_Item.[CatSub];
Where [Active]=-0
Else
If [Forms]![Item_Master]![OptionActive]=3 Then
SELECT tbl_Item.ID, tbl_Item.PLU, tbl_Item.Description, tbl_Item.Size, tbl_Item.Main_Link, tbl_Item.CatSub, tbl_Item.Active, tbl_Item.Item_Added, tbl_Cat_Sub.CatSub_Name, tbl_Item.WES, tbl_Item.CAP, tbl_Item.PAC, tbl_Item.HMB, tbl_Item.EVG, tbl_Item.AllStoreMargin, tbl_Item.AdType, tbl_Item.Notes
FROM tbl_Cat_Sub RIGHT JOIN tbl_Item ON tbl_Cat_Sub.[CatSub] = tbl_Item.[CatSub];
END IF
END IF
END IF
tike55Asked:
Who is Participating?
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.

ste5anSenior DeveloperCommented:
The record source must be valid SQL. IF..THEN.ELSE is not part of ACE SQL.

You have two options:

1. Set the record source in VBA, where IF is available.
2. Use a valid WHERE predicate.

In your case I would opt for 2. Use this as row source:

SELECT tbl_Item.ID, tbl_Item.PLU, tbl_Item.Description, tbl_Item.Size, tbl_Item.Main_Link, tbl_Item.CatSub, tbl_Item.Active, tbl_Item.Item_Added, tbl_Cat_Sub.CatSub_Name, tbl_Item.WES, tbl_Item.CAP, tbl_Item.PAC, tbl_Item.HMB, tbl_Item.EVG, tbl_Item.AllStoreMargin, tbl_Item.AdType, tbl_Item.Notes
FROM tbl_Cat_Sub RIGHT JOIN tbl_Item ON tbl_Cat_Sub.[CatSub] = tbl_Item.[CatSub];
Where ( [Forms]![Item_Master]![OptionActive]=-1 AND [Active]=-1 )
Or    ( [Forms]![Item_Master]![OptionActive]=0 AND [Active]=-0 )
OR    ( [Forms]![Item_Master]![OptionActive]=3 )

Open in new window

tike55Author Commented:
thanks, I will try it.  Is there a source online that defines valid sql statements for Access?
Dale FyeOwner, Dev-Soln LLCCommented:
No, you will not be able to do that in the SQL, however, you might be able to do:

SELECT tbl_Item.ID, tbl_Item.PLU, tbl_Item.Description, tbl_Item.Size, tbl_Item.Main_Link
, tbl_Item.CatSub, tbl_Item.Active, tbl_Item.Item_Added, tbl_Cat_Sub.CatSub_Name
, tbl_Item.WES, tbl_Item.CAP, tbl_Item.PAC, tbl_Item.HMB, tbl_Item.EVG, tbl_Item.AllStoreMargin
, tbl_Item.AdType, tbl_Item.Notes
FROM tbl_Cat_Sub RIGHT JOIN tbl_Item 
ON tbl_Cat_Sub.[CatSub] = tbl_Item.[CatSub]
WHERE([Active] = IIF([Forms]![Item_Master]![OptionActive]=-1, -1, IIF([Forms]![Item_Master]![OptionActive]=0, 0, 99)))
OR ([Forms]![Item_Master]![OptionActive] = 3)

Open in new window

Note that I inserted a 99 in the nested IIF() statements if the control on the form is not = -1 or 0; this assumes that [Active] will never be zero.

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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ste5anSenior DeveloperCommented:
See the official reference: Microsoft Access SQL reference.
tike55Author Commented:
Hi Dale,

I'm a little confused about the 99.  it is an option group so the value will always be -1,0,or 3

Could you explain?
tike55Author Commented:
I would expect a 3 would be inserted in the IIF statement instead of 99
Dale FyeOwner, Dev-Soln LLCCommented:
At the time, I was uncertain what other values the OptionAction button might have, but assumed it would not have a 99.  Since there are only 3 options, then yes, you could replace the 99 with a 3 and then delete the later portion of the WHERE clause.
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.