Switch Function problem

[maisie]

I am trying to write a query using the Switch function. My expression is supposed to check four boolean fields and according to which one is true, return a value between 'a' and 'd'. However the expression is returning the value '1' when it finds the field which is set to true.
This is my expression Expr1: Count(Switch(([Correcta]=True),"1",([Correctb]=True),"2",
([Correctc]=True),"3",([Correctd]=True),"4&quot)
I had to put the field name and true in brackets, as without them I got the error 'data type mismatch'
Any ideas??
Thanks!

 

[MichaelRed]

To get SOME response, just delete the count. To Get the "A", "B", "C", or "D" you need to replace the "1", "2", "3", "4" with the coresponding letters.

Otherwise, this procedure is not really clear, You MAY be wanting to return the NUMBER of booleans which are true OR the 'identity' of the FIRST one in the series which is TRUE, or even the (sub)Set of booleans which are TRUE.

Choose, of course returns ONE from the list of items, while COUNT returns the number of Items, so Count(Choose( will ALWAYS return 0 or 1, thus some of MY hesitancy in response.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BoxHead]

First, take the word true out of brackets and put it in quotes.

I'm not entirely clear on this, but IF only one of these can be true and IF you want to have one column for each record that identifies which one is true, I think the following might work better than the Switch function.

Expr1:IIF([correcta] = "True","1","") &
IIF([correctb] = "True","2","") &
IIF([correctc] = "True","3","") &
IIF([correctd] = "True","4","")

If all values were true, this would return "1234", but if only one can be true at a time, then this should work.


HTH

John

Use what you have,
Learn what you can,
Create what you need.