I know I've seen this problem discussed but can't find it anywhere.
I'm trying to query a table and evaluate only one place at a time in a five character coded string, then output descriptive text in a report based on what is found in that place. Each place in the string represents a different area of study (1st place English, 2nd place Math, 3rd Place Social Studies, 4th Place Science, 5th place Foreign Language). One of seven different characters occupies each place in the string, representing where a student will take their final exam for that class. I set up a small table to give a descriptor for each letter. For example, "R" means classroom, "L" means resource room, "C" means computer lab, and so on. However, I can't figure out how to evaluate say only the 2nd character when I want to report where that student will take the Math exam. A typical code might look like "RRLCR". If I am reporting for the science final, then this student takes the test in the Computer lab since the 4th place represents the science test and the "C" represents the computer lab.
I originally tried setting up code tables for each of the finals with every possible combination paired with the appropriate description of that 5 character code for that particular final. Not only is it cumbersome, but for some reason it is causing errant filtering of data in my query.
I'm trying to query a table and evaluate only one place at a time in a five character coded string, then output descriptive text in a report based on what is found in that place. Each place in the string represents a different area of study (1st place English, 2nd place Math, 3rd Place Social Studies, 4th Place Science, 5th place Foreign Language). One of seven different characters occupies each place in the string, representing where a student will take their final exam for that class. I set up a small table to give a descriptor for each letter. For example, "R" means classroom, "L" means resource room, "C" means computer lab, and so on. However, I can't figure out how to evaluate say only the 2nd character when I want to report where that student will take the Math exam. A typical code might look like "RRLCR". If I am reporting for the science final, then this student takes the test in the Computer lab since the 4th place represents the science test and the "C" represents the computer lab.
I originally tried setting up code tables for each of the finals with every possible combination paired with the appropriate description of that 5 character code for that particular final. Not only is it cumbersome, but for some reason it is causing errant filtering of data in my query.