query problem

[rigore]

attached find small customer database and code. the purpose is to take the Suffixes in the table and put them into the Suffix location instead of being in the name section. You can see the start of the coding after the "END" statement

as you will see the suffix can be in any of the Lname, Fname, or Mi part of the table. I built the query and it finds the 351 items that contain the suffixes that need to be moved.

However when i try to cycle thru the query I only get the first record. the variable PTR does change when I use "ptr = qry.find_next(ptr)" but i can not capture the proper record with the "recn = tblCust.recno()" so I can fetch that record. How do i capture the Key or Record out of the ptr = {Key=Aiken ,Record=10014} to fetch the proper record to modify.

I put debug watch on rec, ptr, recn, tblCust.Fullname, and I so I can verify the cycling.

TIA again

Don

[johnkoh]

Re: query problem

Don,

See enclosed one
tick2.zip

[rigore]

Re: query problem

John thanks for the reply but it does not solve my problem. I need to cycle thru the query to find and remove the suffix from the name and put it in the Suffix slot in the table.

I.E. John Steve Smith jr. lname = Smith jr. Fname = John Mi = Steve Suffix = ""

I need to remove Jr. from Lname and put it in ths suffix slot. I have not coded that part yet because I have been unable to cycle thru the 351 records found by the query.

TIA

Don

[GGandhi]

Re: query problem

hello

do an update operation see if you like it
create a new field for example last_name, character, length 16 as in your Lname field just before the suffix ( to make it easy to read nothing special about the location)
create an operation, update on that table.
last_name, expression word(Lname,1), condition blank
suffix, expression word(Lname,2), condition blank
save the operation with any name you like.
run the operation. and check the results. if you like what you see the you have an option of deleting the Lname and renaming last_name as Lname and move it to the top.

just as a precaution next time eliminate all the private info which do not add to the relevance to the question.

take a look at this:
http://www.screencast.com/t/t7UoKAoDzyj

[johnkoh]

Re: query problem

Don,

Good morning,
I suggest this instead of the full code.

**** Backup your data and

**** make sample data table and have about 5 to 10 records

(1) Find the records from table and one after one. - I gave you the sample
(2) Read the found records with Loop function, "While ... End" or "for ... next" . You will see the records and change here
* Insert this between while ... end
(2-a) Pull out the lastName and suffix from Lname field, with WORD() - see help
*Tip; from interactive, try this

txt ="smith jr"
find = word(txt,2)
?find

Do not copy and paste this sample, Type it

(2-b) Change / replace the current table's records with tbl.change_begin() and tbl.change_end().
***If you are not sure about "tbl.change_begin()" then use ui_msg_dlg() for TESTING your code result.

(3) use "<Query>.drop()" and "<Table>.close()" at end