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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

0
tike55Author Commented:
thanks, I will try it.  Is there a source online that defines valid sql statements for Access?
0
Dale FyeOwner, Developing Solutions 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.
0

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ste5anSenior DeveloperCommented:
See the official reference: Microsoft Access SQL reference.
0
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?
0
tike55Author Commented:
I would expect a 3 would be inserted in the IIF statement instead of 99
0
Dale FyeOwner, Developing Solutions 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.
0
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.