How to use the text created in one query as criteria for another query field (1 Viewer)

[fredalina]

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!

 

[CBrighton]

I would advise dumping the table and creating a form.

A simple form with 3 textboxes and a command button can do what you want, the VBA behind the command button can create the concatonated string based on the contents of the 3 textboxes then use that as the .filter property on your report. (remember to use .filteron to ensure it's actually applied).

That way the query has the static criteria (if there are any) and the form's VBA handles any ah hoc criteria which you want to apply.

There are various threads on "search forms" on this forum, do a quick search for more info on how to create them and example code, etc.

:edit:

Personally one of my databases has a search form with 40-50 possible criteria fields which are for fields across the 3-4 tables which my report queries are based on. It's ideal for allowing the end user to customise the criteria of reports / exports without them needing any technical knowledge (or accidentally altering the query in the wrong way, or not changing it back afterwards, etc).

 

[RaWiKe]

Like CBrighton said using a mulity search form by using form design make ase many bounded text boxes you need (one box for each colum you want to search by) lable the lables and then name the box then leave the controle source blank. in your query in the critera use Like "*" & [form]![the form you just made]![the name of the field of the table you want to use] & "*" here is and example of one of mine.

Like "*" & [Forms]![Status]![Last] & "*"

and do this for all the colums you want to look up on one query and you will be able to look up multiple things up at once and also leave colums blank if you dont need it. if you are using Access 2007 or later as you type in the info it will give you the information to apply.