Switch function problem

[dvh]

Hi guys
I have a problem with filtering a query in criteria using either iff or switch builtin function. my codes look like this

Like Switch([Forms]![frmMain]![grpReq]=1,"SysReq*",[Forms]![frmMain]![grpReq]=2,"ACR*",[Forms]![frmMain]![grpReq]=3,"ACSR*",
[Forms]![frmMain]![grpReq]=4, [B]not like "SysReq*" or "ACS*"[/B])

the function works fine except for value 4 highlighted in bold.
The query just didnt return the result i want. I suspect its the
switch function not liking to evaluate the "Not like" part. I dont know how to go around this as i can only base on the prefix, namely SysReq, ACR, ACSR

Thank you for your ideas...

 

[doco]

not like "SysReq*" or "ACS*")

You need to continue the not like to the 'OR' side as well. OR NOT LIKE "ACS*"

 

[dvh]

actually i tried that already..even after i removed everything leaving only 'not like "SysReq*" ' it still didnt work..

 

[stopher]

I haven't really thought this through but I think this is a case for good old fashioned logic:

(([Forms]![frmMain]![grpReq]=2 And [myField] Like "SysReq*") Or ([Forms]![frmMain]![grpReq]=2 And [myField] Like "ACR*") Or ([Forms]![frmMain]![grpReq]=3 And [myField] Like "ACSR*") Or ([Forms]![frmMain]![grpReq]=4 And [myField] Not Like "SysReq*" And [myField] Not Like "ACS*"))

where myField is the name of the field you are trying to test.

hth
Chris

 

[dvh]

Thanks all for your input. This "Not (Like "SysReq*" Or Like "ACR*" Or Like "ACSR*")" works fine (return the values i want) in a criteria of a query. But when it's used as a "true part" in a Iff function or as "value" in Switch function, it just doesnt work.
Stopher,maybe i didnt explain mysefl clearly. I actually want to use either Iff or Switch function in as a criteria for a query. Depending on the value from the option group on the main form, the iff/switch would use the appropriate filters i.e "SysReq", "ACR", "ACSR" and "none of the prefix"
Can i use the functions in this case or do i have to do other way?
p/s the function works fine with the 1st 3 values. its the last one that didnt work.
Help pls!

 

[stopher]

I actually want to use either Iff or Switch function in as a criteria for a query. Depending on the value from the option group on the main form, the iff/switch would use the appropriate filters i.e "SysReq", "ACR", "ACSR" and "none of the prefix"
Can i use the functions in this case or do i have to do other way?

I think the problem is that for your first three expressions you are passing just the string search, but for the last expression you are passing back an expression that you are hoping will be evaluated:

if grpReq=1 then the your original expression will be evaluated as:
Like "SysReq*"

if grpReq=4 then the your original expression will be evaluated as:
Like not like "SysReq*" or "ACS*")

As you can see it doesn't make sense.

Did you try the logic I gave? It's meant to be the where part of a query i.e. the criteria. It will deal with each case (1-4 as entered on your form). I noticed I had some typos so here it is again:

([Forms]![frmMain]![grpReq]=1 And [myField] Like "SysReq*") Or ([Forms]![frmMain]![grpReq]=2 And [myField] Like "ACR*") Or ([Forms]![frmMain]![grpReq]=3 And [myField] Like "ACSR*") Or ([Forms]![frmMain]![grpReq]=4 And [myField] Not Like "SysReq*" And [myField] Not Like "ACS*")

The expression splits into 4 parts. For each value (1-4) on your form, only one of the parts will be valid (the other three ignored). For instance
[Forms]![frmMain]![grpReq]=1 And [myField] Like "SysReq*"
will only be true when grpReq=1 and therefore Like SysReq* will only be returned in your query when grpReq=1. If grpReq<>1 then SysReq is ignored.

I appreciate this doesn't look as cool as IIF or Switch but it does work (there may be a marginal performance hit). You could write some vba but if it works and there's no performance hit, why bother.

hth
Chris

 

[dvh]

BRILLIANT! thanks stopher..got it workingnow... i agree with you on the function does not want to evaluate the expression i pass. Dont see it will affect performance at all.dont see how.
One setback though..somehow it disables my 2nd level datasheet subform on the 1st level subform. Anyway, will try to figure out.....
cheers

 

[stopher]

One setback though..somehow it disables my 2nd level datasheet subform on the 1st level subform.

Access has a funny habit of losing the plot between form/subform sometimes, particularly if you are changing the datasource. The best way I find of resolving this is to:
- delete the subform from the form
- save the form
- open the form in design view
- add the subform again

hth
Chris

 

[dvh]

spot on Chris!
thats exactly what i did and i got it done
thanks again
cheers mate