supplying a query parameter 1

[dc20]

I have an Access parameter query that calls a module function chk1(). What I'm trying to supply as a parameter via the function is the following 14 characters, including spaces and the 'or' ....

"AAA" or "BBB"

which works when typed directly into the criterion cell for the query, but doesn't when I use it in a function...

Public Function chk1()
chk1 = "'AAA' OR 'BBB'"
End Function

The criteria I have calls this function chk1. If I use the simple statement in the function: chk1 = "AAA" , it does work for that, so the call is ok, just my dang quotes aren't right - assuming an 'or' statement can be supplied programmically. I've tried a bunch of combinations of ' and " and & chr(34) and & chr (39), just haven't got it yet. Please help.

The function will be more involved than this to produce the string, but knowing the answer to this will help me enough for now. I don't get an error, it just doesn't return the data.

 

[cmmrfrds]

Public Function chk1() AS String
chk1 = "'AAA' OR 'BBB'"
End Function

This worked fine for me.

 

[dc20]

This is driving me nuts. It's encouraging that it works for you though, cmmrfrds. I opened a new db file and made it as simple as possible, but it still is not retrieving any data when the 'or' is implemented, ie two criteria.

Here's a screen capture picture of the simple setup and table and the query calling the function. When I run it, no records are returned. If I use the 2nd (now commented) statement of chk1 = "aaa" , it does work - but not for multiple conditions.

http://66.36.242.216/~dcook/images/zmisc/db1_pic.jpg

thanks for any further help.

Dave.

 

[cmmrfrds]

I just checked to see if the function returned the correct string which it does. If you want to apply the criteria to a where clause then one of two ways.

where yourfield = "aaa" or yourfield = "bbb"
OR
where yourfield IN ("aaa", "bbb")

 

[dc20]

cmmrfrds, thanks for hanging in with me. I need a clarification on your statement "..returned the correct string which it does" .

Does that mean you only checked the output of the string or the actual records returned ? If the former, then I agree the debug.print of the string assignment looks right, but the simple table I created (see pic in my prev entry) does not return both sets of records. I'd really like to make this approach work before changing gears to coding/modifying the sql statement since it involves many parameters and columns (but I do understand your suggestion and thankyou for it).

By chance did you try my simple example ? I could upload it to the website or if you have an example working I could download it if you have a website ? There's got to be a simple answer here to what's wrong.

Dave.

 

[cmmrfrds]

If you sql statement(s) looks like this.

dim mysql as string
mysql = "select * from mytable where " & chk1()

Public Function chk1() AS String
chk1 = "yourfield = 'AAA' OR yourfield = 'BBB'"
End Function

OR.
mysql = "select * from mytable where yourfield " & chk1()

Public Function chk1() AS String
chk1 = "IN ('AAA', 'BBB')"
End Function

 

[dc20]

I have converted over to editing the sql text as your last entry suggested.

I'm convinced now what I was trying to do is with assigning chk1 = "'AAA' OR 'BBB'" is impossible. Viewing the sql for such an 'or' statement, I notice the sql distributes the two requirements.

In other words sql produces ...
field1 = 'AAA' or field1 = 'BBB' ,
instead of field1 = 'AAA' or 'BBB'.

So even though the query criteria field appears as..
"AAA" or "BBB",
the sql is actually a distributed statement.

Anyway, the sql method you outlined is working and I thankyou.