By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Switch() with criteria of Like wildcard

P: 78
I have a field whose criteria I can't quite work out.

I have an Option Group on a form. If the value is 2, 3 or 4, then criteria equals as shown below (works fine). The kicker is value 1: I want to return all records (Like "*"). However, using the Switch statement, I can't seem to pass that through. I've tried all sorts of combinations of single and double quotes around the Like "*", but to no avail. I know someone out there knows the quick fix! Thanks.


Expand|Select|Wrap|Line Numbers
  1. Switch([Forms]![Accounts]![PaymentTypeOptionGroupFrame]=1,"Like '*'",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=2,"Current Charges",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=3,"Fuel Card",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=4,"Prepay"
May 8 '08 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
I have a field whose criteria I can't quite work out.

I have an Option Group on a form. If the value is 2, 3 or 4, then criteria equals as shown below (works fine). The kicker is value 1: I want to return all records (Like "*"). However, using the Switch statement, I can't seem to pass that through. I've tried all sorts of combinations of single and double quotes around the Like "*", but to no avail. I know someone out there knows the quick fix! Thanks.


Expand|Select|Wrap|Line Numbers
  1. Switch([Forms]![Accounts]![PaymentTypeOptionGroupFrame]=1,"Like '*'",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=2,"Current Charges",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=3,"Fuel Card",[Forms]![Accounts]![PaymentTypeOptionGroupFrame]=4,"Prepay"
You'll need to make the comparison always a LIKE like:
Expand|Select|Wrap|Line Numbers
  1. where x like Switch(...)
  2.  
Now use for the 1 just the value "*"

Nic;o)
May 8 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
Your Switch() function is trying to return SQL code, rather than simply returning a value. It doesn't work like that I'm afraid, and that's why item 1 will never work for you.

That sort of technique can be used if you're building up a SQL string from somewhere else (VBA for instance) which is itself (later) passed on to the SQL engine to process. It can't can't work within the SQL process itself though.

Nico's suggestion is a very usable alternative.
May 9 '08 #3

P: 78
Works great, thank you very much.
May 9 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
You're welcome :)
May 12 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.