I have a report built off one set of raw data, and I'm trying to verify it based on another set of raw data. One of the fields is "OPTS", which contains alphanumeric codes that identify options for each field (i.e. "X1" or "A8"). Some records can have more than one code in the OPTS field ("A8X1M4", for example). If I want to query anything that contains the code X1, whether it occurs in the middle or the end, I know I can type in the criteria field of my query "Like '*X1*'", and this works fine. Similarly, if I want to query 2 possibilities, I can use "Like '*X1*' OR Like '*M4*'".
However, I am changing criteria frequently, and I have another query built from this first. I don't want to constantly go to design view to change up the criteria. I created a table with 3 fields (OPT1, OPT2, OPT3), and a form that feeds this table. I have a query that creates a concatenated string that basically builds the "Like"/"Or" statement with appropriate *'s based on whether there is text in the OPT2 or OPT3 fields. I will never query more than 3 items together (probably no more than 2). I call the concatenated field OPTIONS.
If I use the text output from the concatenated field and copy/paste it into the criteria for OPTS, it works fine. However, I can't seem to get it to work. I can't set the criteria for OPTS to [OPTIONS] (no records) or =[OPTIONS] (error) or =Str([OPTIONS]) (which is silly anyway because it's already a string, and predictably errors out). Is there a way to make this work?
Thanks!
However, I am changing criteria frequently, and I have another query built from this first. I don't want to constantly go to design view to change up the criteria. I created a table with 3 fields (OPT1, OPT2, OPT3), and a form that feeds this table. I have a query that creates a concatenated string that basically builds the "Like"/"Or" statement with appropriate *'s based on whether there is text in the OPT2 or OPT3 fields. I will never query more than 3 items together (probably no more than 2). I call the concatenated field OPTIONS.
If I use the text output from the concatenated field and copy/paste it into the criteria for OPTS, it works fine. However, I can't seem to get it to work. I can't set the criteria for OPTS to [OPTIONS] (no records) or =[OPTIONS] (error) or =Str([OPTIONS]) (which is silly anyway because it's already a string, and predictably errors out). Is there a way to make this work?
Thanks!