Query Selecting All Records

[Phil Rolf]

Here is a script that when it runs it selects all the records instead of the records that fall between the two dates. Also the query.order will give me an error of field not found. If I comment it out the query will work with the exception that all the records are selected. Any help would be appreciated.

dim tbldate as d
dim begdate as d
dim enddate as d
dim indx as p
dim rec_count as n
dim response as n

tbl=table.current(1) 'Day
tbl1=table.current(2) 'Schedule

askagain:

begdateask = ui_get_date("Beginning Date","Enter Beginning Date",dtoc(date()))
enddateask = ui_get_date("Ending Date","Enter Ending Date",dtoc(date()))

response = ui_msg_box("Correct Find Dates?","You Entered Dates "+begdateask+" thru "+enddateask+" Is This Correct?",35)

IF response = 2
goto bypass
END IF

IF response = 7
goto askagain
END IF

begdate=ctod(begdateask)
enddate=ctod(enddateask)
tbldate=tbl.date

'query.description = ""
query.filter = "between(tbldate,begdate,enddate)"
query.order= "tbl.date"
indx = tbl.query_create()
rec_count=indx.records_get()

If rec_count = 0
ui_msg_box("Not Found","No Records Found Within The Defined Dates")
tbl.index_primary_put("date")
goto bypass
End If

bypass:
parent.resynch()

[Tom Cone Jr]

RE: Query Selecting All Records

Philip,

Use the default form for the set.

Drop a button on the default form and insert your script into it. Does it select the correct records?

If so, that suggests your original form may have hardwired filters or ordering expressions which prevent your script from running.

-- tom

[Bill Warner]

RE: Query Selecting All Records

I think the cause is the way you reference the two variable dates in the BETWEEN statement. I tried your expression and got the same error. Then I put var-> in front of the variables, and it worked.

Also, in the sort expression, just use the field name, and don't preceed it with tbl. Since you've already defined tbl as the current table, the field name alone will suffice.

[Phil Rolf]

RE: Query Selecting All Records

Tom, your idea did not work. However, I did find an ordering parameter that had to be deleted.

Anyway, I finally got it to work. Why, I don't know. I guess I am confused on the do's and don'ts of the tbl prefix. Here is what I did..

In the filter - changed tbldate to date
In the order - changed tbl.date to date

Why do you not need the tbl prefix?

Thanks

[Peter.Wayne]

RE: Query Selecting All Records

you can simply make it

'query.description = ""
query.filter = "between(date,{"+begdateask+"},{"+enddateask+"})"
query.order= "date"

[Phil Rolf]

RE: Query Selecting All Records

With this same code. How can I change the ENDDATEASK default be begdateask +14 days.

begdateask = ui_get_date("Beginning Date","Enter Beginning Date",dtoc(date()))
enddateask = ui_get_date("Ending Date","Enter Ending Date",dtoc(date()))

Thanks

[Peter.Wayne]

RE: Query Selecting All Records

enddateask = ui_get_date("Ending Date","Enter Ending Date",dtoc(date()+14))

[Phil Rolf]

RE: Query Selecting All Records

Peter,

I am trying to add 14 days to whatever date is input to the begdateask parameter, not the date(). I was able to get this to work. Whenever I try to add 14 to the begdateask I keep getting invalid argument.

Thanks
Phil

[Tom Cone Jr]

RE: Query Selecting All Records

Philip,

Looks to me like maybe you're trying to add a numeric value to a char string variable. That's a no no.

To do 'date' arithmetic you must convert the value to date type first.

From the interactive window:

dim begindatetext as C
begindatetext = "01/01/2002"
?begindatetext = begindatetext + 14
=
'
dim begindate as D
begindate = ctod(begindatetext)
? begindate
= {01/01/2002}

? begindate + 14
= {01/15/2002}

newbegindate = begindate + 14
? newbegindate
= {01/15/2002}

[Phil Rolf]

RE: Query Selecting All Records

Thanks Tom
The info you gave me was enough to determine where I was going wrong. I was using the wrong date paramater.

[Tom Cone Jr]

RE: Query Selecting All Records

Great!

-- tom