Help needed with Query by form

[henniec]

Good day,

I am trying to create a form that could be used by the users to create their own queries if needed. The form is based on the example given by Stan Leszynski in his book Access 97 Expert Solutions. I have changed the form to include a list box with the property "Multi select" set to Extended so that the user should be able to choose only the fields he/she requires.

There is five distinct fields on the form of which the last three is repeated four times. I will be adding a sixth field that would be used as a connector (AND or OR)

The fields are as follow.
1. Combo box to select the table or query too be used in the query.
2. List box with the field names of the chosen table/query.
3. Combo box with the field names too be used in the criteria.
4. Combo box with Comparison criteria. (<, =, > ect.)
5. A value field. To complete the criteria. E.g. a date, a number or some text.

I have two questions.

How do I code the following. If I select say four fields in the list box to be included in the the query, tried &quot;& Me!List5.Value &&quot; and it does not work. How would I include, in code, quotation marks in the Value field if text is entered. Access would normally correct the information if a date or number is entered, but, if text is entered the word is enclosed by square brackets. E.g.

SELECT * FROM qryCustOrder WHERE (((qryCustOrder.CompanyName)=[abc])); instead of

SELECT * FROM qryCustOrder WHERE (((qryCustOrder.CompanyName)=&quot;abc&quot);

Thank you for your time.

Hennie

 

[amcodah]

I'm not sure if I did get your question right. But is this what you wanted?:

Me.CompanyName = &quot;[&quot; & Me.CompanyNameField & &quot;]&quot;


Please explain better an shorter if not

 

[henniec]

No, it is not. What I need to know is how to tell Access that in that field of choice is is text. I have the three boxes. The first two are combo boxes where a choice can be made which could be say Sampledate, the second > and in the third I enter a date, say, 22/07/3003. Access will correct that in the query as #22/07/3003#. If I make a second choice, say Number of days (first combo box) and = (second combo box) and 6 in the third, Access once again assume it is a number. No problem with that.

But as in the above mentioned case if the third choice is Company name and I enter abc in the third box Access assume it as a field name and not as a criteria. What I need to know how to tell Access that the field name (Company Name) in the table is dbText and now I have to enclose whatever is in the third box with quotation marks.

I hope it explains it better.

Thanks.

Hennie