Query on date

[scottly]

Can anyone point out the error of my ways in the below - my intention is to delete any records in table r_races where rm_date (a field in the table) is equal to the current days date.

Regards,
Scott

t2=table.open("r_races")
Dim ws_date as D
ws_date=date()
query.filter="rm_date=ws_date"
query.order="rm_date"
query.options="M"
qry_races=t2.query_create()
nrecs=qry_races.records_get()
if nrecs>0 then
t2.delete_range()
end if
qry_races.drop()
t2.close()

[Peter.Wayne]

RE: Query on date

the query.filter is a full character field, so you have to construct it as

query.filter="rm_date={" + dtoc(ws_date) + "}"

you could also use the replace_parameters() function instead by writing
filter="rm_date= [varD->ws_date]"
query.filter=replace_parameters(filter)

which, in my mind, is no easier than just creating the filter expression yourself!

[scottly]

RE: Query on date

Hi Peter

I changed my query.filter line as you suggest to:

query.filter="rm_date={" + dtoc(ws_date) + "}"

But still nrecs=0 (although there are definitely records in the table with todays date)

I have checked that rm_date is defined as a Date field (and is also defind as a key).

Under debug, query.filter contains:

rm_date={13/11/2003}

Any thoughts?

Regards,
Scott

[scottly]

RE: Query on date

If I use the second method;

filter="rm_date= [varD->ws_date]"
query.filter=replace_parameters(filter)

Under debug, on the second line I get a message "Not an executable file"?

Regards,
Scott

[Stan Mathews]

RE: Query on date

This looks like a British/European date format? rm_date={13/11/2003}. Is the data stored the same way in the table?

[scottly]

RE: Query on date

Hi Stan

I am in Australia - yes the date is stored in the same format.

Regards,
Scott

[Stan Mathews]

RE: Query on date

Should I type louder since you're so far away?

Since you still reported a problem, I tried the code on one of my tables, with no problem, evaluated correctly.

Can you post a sample of your table/set here to test?

[scottly]

RE: Query on date

I shall post an example with the form and test data this evening - thanks.

[Peter.Wayne]

RE: Query on date

try putting in
query.filter="rm_date={11/13/2003}"
and see if the query returns anything (using US-style dates).

[scottly]

RE: Query on date

I have attached the Form etc and test data for this issue. I have commented out unrelated code. See code behind the 4th ICON/button from the top left on the form. The idea is to delete any records with the current days date before loading information from a web page.

This Form also has the 'known issue' where deleting a single record via the Forms Delete button refreshes the browse causing the browse to display from the first record - this behaviour makes it impossible to browse through the records and selectively delete records, since you lose your position in the browse each time you delete a record. The folks at alpha told me they could not replicate the issue when reported on another Form - and yet again here it is causing more grief. Gerry has previously suggested a work around but I cant get that to fly. If it is a quick one then any guidance to fix this second issue would also be greatly appreciated.

Regards,
Scott

[scottly]

RE: Query on date

Using query.filter="rm_date={11/13/2003}" results in a message of "invalid date constant" when the query is executed - which you may have expected.

Regards,
Scott

[Peter.Wayne]

RE: Query on date

you have an invalid default value field rule for the field, rm_date, and that is confusing alpha five. remove the default value rule for that field and the query should work.

[scottly]

RE: Query on date

I have removed the default on rm_date in the field rules area but unfortunately that has not made any difference to the result.

BTW: what do you mean by an invalid default date? Are you saying that using "date()" (which works from the point oif view of defaulting the date on a Form) is not the correct way to do it? or, one should not define a default date at all on a field level rule?

Regards,
Scott

[Pat Bremkamp]

RE: Query on date

Scott,

You might try
query.filter="between(rm_date,ws_date,ws_date)"

Pat

[Pat Bremkamp]

RE: Query on date

Scott,

Regarding the browse refresh, you could mark the records as you browse through, then delete all at once at the end.

Pat