Problem using query_create()

[Stephen Allard]

I have the following code fragment on the init event for a form

tbl = table.current()
query.description = "Find records for depreciation"
query.Order = "Dep_for_Year_Ended"
query.Filter = "((Dep_for_Year_Ended < FinYearEnd) .and. Depreciated <> .T.) .and. Dep_Value > 0"
query.Options = ""
ndx = tbl.query_create()

The same code works on other forms, and looks the same as the reference material I have read... problem is that when the ndx = tbl.query_create() line is read I get an error that says "no such field"..

I do not understand.... any ideas?

[Tom Cone Jr]

Stephen,

the form may not be completely initialized when the code runs.

Try this. Instead of opening (viewing) the form...

Open the table, run your query, then open the form (without its onInit script).

Alternatively,

Load your form (hidden), without the onInit script
Run the query
Resynch the form
show the form.

Alternatively,

Delete the OnInit script
Embed the query expression into the Form's Filter & Ordering Properties

-- tom

[Stephen Allard]

Problem using query_create()

Tom, thank you for your prompt reply - it is much appreciated.

I tried using the query_create() as described in the first posting on on_push event which initiates the form, and in a few other places... but I keep getting the error message "no such field" when the line "qry = tbl.query_create()" is encountered... even though I have DIMed all the query dot variables and put in the code exactly as per the reference texts and the same as I have used it in other places... is there a possibility that I have some data corruption or am I doing something wrong?

I have not tried to put the filter on the form as per one of your suggestions... call me stubborn, but I would like to get the query_create() to work!

Steve

[Tom Cone Jr]

Stephen, I suspect something in the context of your form is different in this case. Also, it's possible that this exact filter string is slightly different from those that work elsewhere.

Here are some of the ideas that come to mind:
1) table.current() may not be returning a pointer to the table you're trying to query. It will return a pointer to the primary table in the set supporting your form.

2) despite telling us that this expression works in other places, I'm suspicious of it. Instead of:

"((Dep_for_Year_Ended < FinYearEnd) .and. Depreciated <> .T.) .and. Dep_Value > 0"

I'd think this would be better:

"Dep_for_Year_Ended < FinYearEnd .and. .NOT. Depreciated  .and. Dep_Value > 0"

If these ideas don't help it's time to post a working copy of the app, with instructions for us to follow to see the problem.

--tom

[Finian Lennon]

When you say you have dimmed the query. variables, what have you done exactly? The query. variables are, by definition, global variables and I wonder if, by dimming them in another way (other than global) it might affect things the way you see.

For that, among other reasons, I no longer use the query_create method. I find that the <tbl>.order method is much simpler and doesn't have the potential for "carryover" problems that the query. variables can introduce.

Finian

[Stephen Allard]

Stephen,

the form may not be completely initialized when the code runs.

Try this. Instead of opening (viewing) the form...

Open the table, run your query, then open the form (without its onInit script).

Alternatively,

Load your form (hidden), without the onInit script
Run the query
Resynch the form
show the form.

Alternatively,

Delete the OnInit script
Embed the query expression into the Form's Filter & Ordering Properties

-- tom

Hi again Tom. Just thought I would let you know that I managed to get that pesky query to work... one of the things you suggested was that the pointer variable was not pointing to the correct table. This turned out to be true, but I have no idea why and could not fix it... but when I ran a saved query it worked!

All is well that ends well - thanks again for you help. Steve

[Stephen Allard]

When you say you have dimmed the query. variables, what have you done exactly? The query. variables are, by definition, global variables and I wonder if, by dimming them in another way (other than global) it might affect things the way you see.

For that, among other reasons, I no longer use the query_create method. I find that the <tbl>.order method is much simpler and doesn't have the potential for "carryover" problems that the query. variables can introduce.

Finian

Thanks Finean, I DIMmed the query. variables in the form "dim query.filter as C", so nothing there to confuse the issue I think. I managed to find out (as I explained to Tom) that the problem was the pointer variable not pointing to the correct table - but I have no idea why and could not get it to behave differently despite trying just about everything in the book. A saved query run from the same place did work, however! One of the weird things that a new user encounters because of his clumsy work, I expect. Thanks for the tip on using <tbl>.order instead of query_create()... I will try that next time.

[Finian Lennon]

I'd be curious to see the whole script ... though I guess the point is moot.

Dimensioning those variables as local would thwart the program's intention for them to be global and could give rise to the error message you got.

The most common way this shows up is if you do the following in a form where, for example, button 1 has code
t = table.open(tableA)
query.filter = "Inv_Number = '000234'"
query.order = "Inv_Number"
idx = t.query_create()
t.close()

and then button 2 has the code
t = table.open(tableb)
query.filter = "ID = '000884'"
idx = t.query_create()
t.close()

The code on button 2 will give a "no such field error" because it will apply the global query.order to the second query, where the Inv_Number field no longer exists. It's easy to avoid by always setting the query variables = "".

Finian