Is it possible to make an expression in a query that looks at 5 fields in that same query and finds the one that equals one and makes the name of that field the results for the expression?
Thank you for all the help!
Is it possible to make an expression in a query that looks at 5 fields in that same query and finds the one that equals one and makes the name of that field the results for the expression?
Thank you for all the help!
Probably, but readers need some context/code/description to offer more focused response.
Only in VBA.
I figured it might have to be in VBA. Can you tell me how the code would look like? Let say as and example the field names are Field1, Field2, Field3, Field4, and Field5. How do I tell it to look at these fields and which ever one equals 1 use that field name as the result in a field called Results in a form.
Thank you,
As orange says, some context would be helpful. The values in this column will always be 1 so only the column heading is any use to you. That means that you would have 5 different sets of data, all records with field1 = 1, all records with field2 = 1, etc. You can't have more than one field name per query so it would be 5 queries.
The query will only have one row and only one of the fields will have a number, the rest will be null or "". I just need it to find the field that has a number and return the field name. Hopefully that makes sense. I think it can be done using a single query.
Open a record set
Loop through fields to find when the value is 1.
Then take the fieldname from the record set.