• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 66
  • Last Modified:

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
0
tike55
Asked:
tike55
  • 3
  • 2
  • 2
1 Solution
 
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 FyeCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now