Using .AND. & .OR. in a Query Filter

[draxx]

I'm putting together a script and having a problem with the use of .AND and .OR. in a query filter. If I remove the ".OR." statement from the filter, the query works perfectly. If I insert the ".OR." statement, it returns records for only the ".OR." criteria, and ignores the other criteria in the query. Obviously A5 does not like to see both in a query filter. Below is my script so far:

dim tbl as P
dim tbl1 as P

tstr1 = ui_get_text("Client Name","Please Enter Client")
if tstr1=""then
end
end if
user1=(tstr1)

dstr = ui_get_date("Date Received","Please Enter Date In")
if dstr=""then
end
end if
user2=ctod(dstr)

tbl=table.open("ameregl3")
tbl1=table.open("tasks")

'debug(1)

tbl.change_begin()
ameregl3->job = tasks->tasks
tbl.change_end()

'tbl=table.current()

query.description = ""
query.order = ""
query.filter = "AMEREGL3->ATTORNEY=(user1).AND.AMEREGL3->DATE_IN=(user2).AND.ameregl3->code=\"cd\".OR.ameregl3->code=\"adne\""
query.options = "I"
tbl.query_create()

Even though I'm not declaring that "(user1)" and "(user2)" are variables, the script works perfectly without the .OR. statement. As soon as the .OR. statement is added it only filters for that part of the filter. Any ideas for a workaround, or another way to state the filter? I thought perhaps a "CASE" statement might work but am unsure of how to put it together. Also, a CASE statement here would probably return true or false and I don't think that would work either. Thanks for reading the post.

[Al Buchholz]

Re: Using .AND. & .OR. in a Query Filter

3 words for you. algebra - boolean logic

query.filter = "AMEREGL3->ATTORNEY=(user1).AND.AMEREGL3->DATE_IN=(user2).AND. (ameregl3->code=\"cd\".OR.ameregl3->code=\"adne\")"

I'd also suggest that you use debug(1), the trace window, or a messagebox to show the value of query.filter

[draxx]

Re: Using .AND. & .OR. in a Query Filter

THanks Al..... I'll give it a try...but.... I sucked at Algebra....lol.

[draxx]

Re: Using .AND. & .OR. in a Query Filter

Thanks loads Al, that was the ticket. You rock and definitely something else to file away for future scripting......Thanks again,
Scott

[Stan Mathews]

Re: Using .AND. & .OR. in a Query Filter

I find it likely that you are coding this for a button on a form based on a set where amergl3 is the parent and tasks is the child. If this is so.......

tbl=table.curent(1)
tbl1=table.current(2)

'debug(1)

tbl.change_begin()
tbl.job = tbl1.tasks
tbl.change_end()
prentform.refresh()

It will be confusing later, or would to me, to revisit code that mixes form methods such as

ameregl3->job = tasks->tasks

and table methods such as

tbl.change_begin()

[Tom Cone Jr]

Re: Using .AND. & .OR. in a Query Filter

Among other reasons why Al "rocks" is that he's spent a bit of time looking at the help file documentation. The examples in topics "Logical .and." and "logical .or." illustrate the importance of grouping terms with parentheses when appropriate. Recommended.

[Al Buchholz]

Re: Using .AND. & .OR. in a Query Filter

THanks Al..... I'll give it a try...but.... I sucked at Algebra....lol.

We are living in the most practical example of an Algebra world when we work with software and databases.

For sum this is more fun than Math class. Of course no one here had me for their Math instructor.

[draxx]

Re: Using .AND. & .OR. in a Query Filter

Thanks Tom