Passing a string to a query

[Hawkide]

I am trying to pass an string such as Lab1 AND Lab2 to the criteria of a query.

The string will come from a textbox on a form. The user will enter something like Lab3 AND Lab5 AND Lab6 to the textbox. When the query is run, it should read the textbox and use it for the criteria of the Lab field.

I have the following for the criteria in the query:

[Forms]![frmLab]![TxtLabs]

It works if the user types Lab1, but it does not return any records if the user types Lab1 AND Lab2

Why does it not work with the AND?

 

[MoLaker]

If the user enters Lab1, the string being passed to the query is "Lab1". If Lab1 AND Lab2 is entered, the string being passed is "Lab1 AND Lab2" not "Lab1" AND "Lab2". In other words, the AND operator you want to use is not an operator at all. It's just a part of the string. You will have to use multiple fields or process the field's string in some way to get the result you want.

 

[RoyVidar]

I would consider using a multiselect listbox showing all the possible values to filter on, then use the where condition of the object type you're going to use this in (form/report) and pass the criteria.

See for instance thread702-787778 or this faq faq181-5497 by FancyPrairie for how.

Are you sure the AND operator is the correct operator? Wouldn't OR be more appropriate?

Roy-Vidar

 

[Hawkide]

Thanks guys...Bear with me, I am a newbie

If I use a multiselect listbox, I know how to loop through each selected item, but I am not sure how to send the info to the criteria of my query.

I looked at FAQ181-5497 (very nice utility btw) but that seems to be for passing criteria to a form or report (not a query).

I could use multiple fields (as MoLaker suggested), but it would require many fields in many queries. That solution is not very elegant in my case. I am stumped...

 

[MoLaker]

Are you attempting to pass criteria to a pre-defined query as a parameter? If so, it won't do what you want. Anything you pass as the parameter will be treated as the data-type described in the query definition. I'm pretty sure you cannot break it up with operators like you intend regardless of using a multi-select listbox or multiple fields. Instead, I think you will have to create the SQL on-the-fly.

What are you hoping to do with the returned records - view in a form or use in a report? It may be using a filter might accomplish what you want.

 

[Hawkide]

Well, with your help I came up with a solution. Yes, I am trying to "pass criteria to a pre-defined query as a parameter"...

Not sure how elegant this is, but it works...

I created a multiselect listbox on frmLab. Then I created 20 textboxes on the form and set theire visible properties to No. On the Lost Focus event of the listbox, I copy each selected value to one of the textboxes (ie if the user selects 4 items in the listbox, 4 textboxes will have a value and the remainign 16 textboxes will be empty). Then, I set the criteria in the query as:

[Forms]![frmLab]![txb1] Or [Forms]![frmLab]![txb2] Or ... Or [Forms]![frmLab]![txb20]

The query results are used to create a chart on a report. Thanks for your help guys, I couldn't have done it without you . I am gonna look like a star when I bring this to my next meeting...