Switch() with criteria of Like wildcard

**nico5038** · May 8 '08, 08:52 PM

Originally posted by kpfunf

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.

[code=sql]Switch([Forms]![Accounts]![PaymentTypeOpti onGroupFrame]=1,"Like '*'",[Forms]![Accounts]![PaymentTypeOpti onGroupFrame]=2,"Current Charges",[Forms]![Accounts]![PaymentTypeOpti onGroupFrame]=3,"Fuel Card",[Forms]![Accounts]![PaymentTypeOpti onGroupFrame]=4,"Prepay"[/code]

You'll need to make the comparison always a LIKE like:
[code=sql]
where x like Switch(...)
[/code]

Now use for the 1 just the value "*"

Nic;o)

**NeoPa** · May 9 '08, 01:18 PM

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.

**kpfunf** · May 9 '08, 01:41 PM

Works great, thank you very much.

**NeoPa** · May 12 '08, 08:44 AM

You're welcome :)

Switch() with criteria of Like wildcard

Switch() with criteria of Like wildcard

Comment

Comment

Comment

Comment