Using an IIf expression to check a column with multiple values

[Nanuaraq]

We do checks for errors on customer's shipments and run a reporting registry on errors found.

Each check may result in one or more errors identified. It may also result in no errors identified.

I have an access table that contains information about each check done. In the table, there is a column "Errorsfound" that keeps information about whether each particular revealed an error, if there were more than one error, and the nature of the error.



In the column "Errorsfound", the user may choose to check one or more values:
-No errors found
-Error A
-Error B
-Error C

(Of course, logically it will make no sense for the user to check both the value "No errors found" and the value "Error A", while it would make sense to check "Error A" together with "Error B" since both can be present at the same time. However, I do not want to leave "No errors found" out, since I would then have no way to see if the user has chosen to report no errors or the user has forgotten to report.)

Now;
For ease of reporting, I would like a column that reveals whether the particular record has any errors. Let's call this column "Any_errors_found". I would like the column to display the value "1" if "Errorsfound" has one or more of the values that signifies that errors were found. I do not want the value "1" to appear if "Errorsfound" has the value that signifies that no errors were found.

I have tried making an updating query that inserts the value "1" into a column if the column "Errorsfound" contains either "Error A", "Error B" or "Error C". This works, but it quickly becomes impractical as I have to run that updating query regularly. I can do it with a macro, but it is still impractical.

I would like instead for a calculated field to show if any errors were found. I tried to do this using an "IIf" expression. However, cannot seem to make a calculated field that is based on whether the column "Errorsfound" has a particular value.

Why is this, and what do I do instead?

I have made an image of how this would ideally look:

[ranman256]

find 'error' with space:

iif(INSTR([ErrorsFound],"Error ")>0,1,0)

[Nanuaraq]

find 'error' with space:

iif(INSTR([ErrorsFound],"Error ")>0,1,0)

Access doesn't seem to recognize ErrorsFound. When typing the expression in the expression generator, usually the column name will pop up when starting to type its name, but when typing ErrorsFound nothing auto-completes its name, and I can't seem to make the line of code work. This is not only for IIf expressions, I can't seem to use the name of column ErrorsFound in any generated expression.

[CJ_London]

sounds like you are using a calculated field in a table. Suggest use a query instead. However since you appear to be using a multivalue field, I'm not sure you can do that. You'll have to try it and see.

[Micron]

whether each particular revealed an error

Your data should be structured like

particular	ErrorType	ErrCount
1	A	1
1	C	1
2	A	1
3	B	1
3	C	1
4	A	1

The form should not allow user to not make a choice from a listbox of errors. If there are no errors, you could skip writing that particular to the errors table, or use an identifier for no error and insert 1. This could allow you to count any type of error, including those where there were none. I think I can safely say that most of us here would use neither calculated fields in tables, nor work directly on them over using forms.

[Nanuaraq]

Apparently, it is not possible for me to use a calculated field on basis of data from a multivalue field. I have tried to do that in a table, and I have also tried to use a query that queries the multivalue field and then makes an expression based on the multivalue field in the query. I am told by access that this is not valid.

Might it be possible for me to convert the multivalue field to a text string? I have had no success in doing that, but it would solve my problem. How do I convert a multivalue field into text by use of a calculated field?

[CJ_London]

it should be a separate table as micron suggested in post #5 and displayed as a subform on your main form

converting to text string is not a good way to go, you will just be building more problems for yourself and as such not something I would be prepared to help with.

You can display the results of the table as a text string - google 'vba concatrelated' for the process

[Nanuaraq]

created new thread for the particular issue