Use Strig as criteria in Query

[dzepeda]

I have a string with the criteria in a select query (source of another) something like "

.Field1 <> 'X' and

.Field2 <> 'XX'&quot;.

I'm not able to make the query through code, 'cos It's a part of a second query, and that part of another... I try to make a function who pass the parameter to the query, but it's passed like a string(with the quotes) and is not evaluated. I try to use Eval function, but it says &quot;Unknow&quot;.

Any ideas? Thanks

 

[DougP]

Need more input????
What do you want to end up with ?

DougP, MCP

 

[dzepeda]

The idea is select only the recors where Field1 is diferent of a serie of values: i.e.

Field1 Field2
------------------------------
a ! 1
b ! 2
a ! 3
d ! 4
h ! 10

1) Select only the fields <>&quot;a&quot;

2) Select only the fields <>&quot;a&quot; and <>&quot;b&quot;

3) Select only the fields <>&quot;b&quot; and <>&quot;c&quot; and <>&quot;d&quot;

the format of the sentence is not the problem, the problem is when I pass the value to the query it's not evaluated and the query compares the string &quot;

.Field1 <> 'a' and

.Field2 <> 'b' &quot; with <>False and the expresion is always true, and the query select all the record every time.


¿?

 

[OhioSteve]

Its a little bit hard to follow your English. However, I think that you are trying to pass a string from a form into the parameters of a query.

There are many ways to do this. I would probably make the criteria in the query refer to a field on the form. You can do this as follows:

1)Open query in design view
2)click on the magic wand
3)search through the directories until you find the object

You will then have code like this in your criteria: Forms![Form1]![Text0]

Now the query will have a criteria based on the contents of the field.

You may think &quot;Gee, I don't want a another field to appear on my form.&quot; No problem. Just set the visible property on the form to false. Also, remember that the form field is an object in VB. So you can work with it in that environment, if neeeded.

 

[dzepeda]

OhioSteve

Thanks for the tip, but probably my bad english cause I can express my problem appropriately:

The criteria is a combination of values of one field of one table(And - Or combination) and not only a fixed value. In code:

'StrQuery is a Public Variable

StrQuery=&quot;[Table].Field1 <> 'a' and [Table].Field1 <> 'b' or [Table].Field1 = 'z'&quot;

Public Function PassValueToQuery() as String
     PassValueToQuery=StrQuery
End Function

Then in the Query I compare PassValueToQuery() with False

In my logic Access 97 may evaluate the string (Like it does when I do it through code with Docmd.RunSQL), but only compares the string chain and always returns all records.

 

[flugh]

Maybe you need to seperate you query a bit. I don't know how to do a 'nested' logic in a query. Seperate your AND and OR stuff. Like in math/algebra, put some parentheses in there (sorry, I don't know how exactly!). That query says 'take anything that isn't &quot;a&quot; and isn't &quot;b&quot; OR is &quot;z&quot;'. That boils down to 'take anything from c through z' (which is probably just about all your records?).

Just some thoughts, not much solution there. Sorry.

----
JBR

 

[dzepeda]

flugh:

Like I say previously the logic of the sentence is not the problem. I put the generated sentence manually in the query and It works exactly as I want, the problem is to make this through a parameter(The sentence is generated automatically)

 

[OhioSteve]

So to get through the test, A must be true. In addition, B must be false or Z must be true. This should be easy to create in the query builder, and you say that it works when you put it into a query.

Here's a question for you: why not use a traditional query?? You can always call it in code using docmd.query.