Problem with Script Doing Nothing

[draxx]

I'm having a problem with a script I'm trying to run. The script is as follows:

dim Tbl as P
dim user1 as C
dim begindate as D
dim enddate as D

tbl = table.open("ameregl3")

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

dstr = ui_get_date("Beginning Date?","How Far Back Do You Wish To Go")
if dstr=""then
end
end if
begindate=ctod(dstr)

dstr = ui_get_date("Drop Dead Date","Please Enter Drop Dead Date")
if dstr=""then
end
end if
enddate=ctod(dstr)

tbl = table.current()
query.description = ""
query.order = ""
query.filter = "AMEREGL3->ATTORNEY=\"(user1)\".AND.BETWEEN(AMEREGL3->DROP_DEAD,(begindate),(enddate)).AND.ISBLANK(\"AMEREGL3->DATE_COMP\")"
query.options = "I"
tbl.query_create()

END

When I tried debugging, once I got to "tbl.query_create() and hit F12, it shoots straight down to "END" without performimg the query. I've never come across this particular problem before. If I run the query from the "Query Genie", it operates as it should. There should be 212 records. The query should show any records under those parameters that are blank in the "Date_Comp" field. My intention is to run this query then export the records to an excel file. When I create a script with a query, the first thing I do is to create the query and test it before going any further. I even thought about breaking the filter down to 3 separate queries within the script but when I tried that, the queries would query the complete table and not my current table with the results from the previous query before it even though I had "tbl=table.current()". I'm stumped on this. It seems to me that my original query should work, but it seems to do nothing. It takes just one second to run and says "0 Records". Any input would be very helpful. Our database has over 78,000 records and is quite extensive so I know the query should take just a bit, certainly more than almost instantaneous. Thanks for any help guys. I'll be leaving my office but will check the forum and get back to any replies as soon as I can.... Thanks....
Scott

[Stan Mathews]

Re: Problem with Script Doing Nothing

query.filter = "AMEREGL3->ATTORNEY=\"(user1)\".AND.BETWEEN(AMEREGL3->DROP_DEAD,(begindate),(enddate)).AND.ISBLANK(\"AMEREGL3->DATE_COMP\")"

You're probably wanting

query.filter = "AMEREGL3->ATTORNEY = "+quote(user1)+" .AND. BETWEEN(AMEREGL3->DROP_DEAD,{"+dtoc(begindate)+"},{"+dtoc(enddate)+"}) .AND. ISBLANK(\"AMEREGL3->DATE_COMP\")"

[draxx]

Re: Problem with Script Doing Nothing

Hi Stan, Sorry it took so long to get back but I had to go out of town. I hate to sound like a complete newbie but could you please explain to me why the dates need to be taken back to a character, and what the pluses before and after are for. My knowledge regarding scripting does not put me into a complete"newbie" class but probably closer to interrmediate. If you could explain those two things to me I would be extremely grateful. Definitely something to file away for future useage. Thanks again for the reply.
Scott

[Stan Mathews]

Re: Problem with Script Doing Nothing

A query.filter expression must be a quoted string. Inside that string everything is taken as literal text. This means that variables used in the string lose their context as variables.

Try something simple in the interactive window.

user1 = "George"
'simulate a filter expression
query.filter = "firstname = user1"
? query.filter
= "firstname = user1"
'now break out the variable by concatenating the variable with the 
'parts of the string preceeding and following it
query.filter = "firstname = "+user1+""
? query.filter
= "firstname = George"
'closer to what we wanted but a character filter must have the quotes
'around the character value
query.filter = "firstname = "+quote(user1)+""
? query.filter
= firstname = "George"
'now you can change the variable's value
user1 = "William"
'and the filter will "see" the change
query.filter = "firstname = "+quote(user1)+""
? query.filter
= firstname = "William"

When you implement the same for a numeric variable you don't need quotes around the value. When you do it for a date variable you handle the {} in the text

" .AND. BETWEEN(AMEREGL3->DROP_DEAD,{"+dtoc(begindate)+"},{"+dtoc(enddate)+"}

and change the date to character representation of the date value so it can be concatenated into the string.

dt = date()
query.filter = "Today's date is dt"
? query.filter
= "Today's date is dt"
query.filter = "Today's date is {"+dtoc(dt)+"}"
? query.filter
= "Today's date is {09/25/2013}"

In many instances Alpha will automatically convert data types when doing string concatenation but I find it best not to rely on it.

[draxx]

Re: Problem with Script Doing Nothing

Thanks so much Stan for the explanation. I can see why you shouldn't necessarily rely on Alpha to change it. Sometimes it's so frustrating not knowing enough to complete the task at hand. But I must admit, it's a blast discovering how to make it work. Alpha is still the best relational database out there imho. Many thanks again.
Scott