What went wrong with Query by Example?

[jlockley]

In a recent query by example I managed to replace ALL of the titles in my title field with "Executive Chef" rather than, as planned, replacing only those marked exsh.

I seem to have backed everything up about a week ago, so it's only a small tragedy, but I need to standardize some phrasing and don't want to go through what I am about to undertake to restore everything again. Thanks.

(I usually have about a weekly backup, but I did it before I realized my mistake)

 

[Langley]

did your query blank say:

exsh, changeto "Executive Chef"
Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

jlockley,

Let me add a small bit to Mac's example, just in case you need to do something similar in the future.

To limit QBE queries to specific sets of records, first determine a condition that matches the ones you want to change. In this case, that's the set of records containing "exsh" in the title.

Next, type the value you picked as the first parameter in the field where that value is stored, e.g. the Title field in your table.

Now, add a comma, type CHANGETO, and then add the new value you want to use. Note that you need to put multi-word values in double-quotes.

More formally, the first parameter in a QBE field is treated as a limiting condition, e.g. only records matching that value will be affected by the rest of the query.

Also, it's really a good idea to make a backup of your table(s) before doing large scale changes. I'm pretty retentive about doing that these days, and that's only after being in similar circumstance, e.g. losing days worth of data-entry because I forgot to make a backup.

Hope this helps...

-- Lance

 

[jlockley]

Langley: Yes, that is definitely what I remember. Obviously something went wrong. All of a sudden there are 9,934 Executive chefs and no cooks, managers, maitre d's or sommeliers in the thing. '

I usually have backups floating around all over the place. I will in future back up whenever I run a query to change a file. I should have then.

So, footpad: the successful query would read ..exch.. exch, changeto "Executive Chef" ?

 

[footpad]

jlockley,

Depends on the underlying data. Queries are case sensitive, so if your titles really are "exch," then you'd want:

exch, changeto "Executive Chef"

If some titles are in all-caps, then you'll want:

exch.., changeto "Executive Chef"

If you want to change any value containing "exch" anywhere in its value, then (and only then) you'll want:

..exch.., changeto "Executive Chef"

Remember that .. is a wildcard match; it'll match anything at all (including nothing).

If it helps, I used to call is "condition, action" syntax. The first value limits the matches that the query operates against and the action tells it what to do with the matches.

Hope this helps...

-- Lance

 

[jlockley]

It's what I did, but obviously I did it wrong. I am going to try again on a backup.

I have another question regarding changing partial records. I have taken and altered this for the purpose of removing a comma from a names field. The field (Full Name) currently reads Doe, John. I would like it to read Doe John. It returns the error TCursor not opened. What's wrong.

Or rather, is there a way to change a specified string or character in some or all of the records of a field while leaving the rest?

Here my failed attempt:

method run(var eventInfo Event)
var
tc TCursor
ar Array[] String
s, news String

endvar

tc.open("sFPFSMembers2002bkp.DB&quot
tc.edit()

scan tc for not tc."Full Name".isBlank():
s = tc."Full Name"
s.breakApart(ar, ",&quot
if ar.size() = 2
then
news = ar[1] +" "+ar[2]
tc."Full Name" = news
endIf
endscan

tc.endedit()
tc.close()


endMethod

Note that I also tried a changeto query here with no results.

 

[Langley]

Your working directory is probably not right. Try using the full path or alias to the "sFPFSMembers2002bkp.DB" table. A Changeto won't work here - I'll study your code a bit. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Actually I have a suspicion part of it may be yours.

 

[Langley]

Maybe, though I never use 'scan for', just 'scan' (I do my conditionals inside the scan loop). Conceptually the code looks okay to me, but you really should use error trapping, especially when opening tCursors and such. It will alert you to any problems quickly.

i.e.

if not tc.open("sFPFSMembers2002bkp.DB&quot
then errorShow()
return
endif


Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

It worked. Many thanks!