SET NEAR ON 1

[StacyStacy]

Can someone help me with the SET NEAR ON command. The following code works, but it will not return a record that has "Fred Flinstone" in the field, "task". The end result tells me that there are no records found.

Here's the code for the Find button:
SET NEAR ON

IF m.find = 1 AND NOT EMPTY(m.findthis)

SELECT * ;
FROM bashproc ;
WHERE bashproc.task = ALLTRIM(m.findthis) ;
INTO CURSOR pickone

IF _tally > 0 && I found at least one record that matched......
SELECT pickone
BROWSE
m.f1=pickone.task
m.f2=pickone.compdate
SELECT bashproc
SET ORDER TO TAG newkey
IF SEEK(ALLTRIM(pickone.task)+DTOC(pickone.compdate),"bashproc")
SELECT bashproc
SCATTER MEMVAR MEMO
SET ORDER TO
CLEAR READ
RETURN
ELSE
WAIT WINDOW "Seek Into BASHProc Failed"
ENDIF

ELSE
WAIT WINDOW "No Matching Record(s) found"
ENDIF


* SET NEAR ON
* INDEX ON task TAG Task ADDITIVE
* SEEK ALLTRIM(m.task)
* SET NEAR OFF
* IF NOT FOUND()
* GO BOTTOM
* WAIT WINDOW "The Query You Entered Was Not Found!" NOWAIT
* ENDIF
ELSE
WAIT WINDOW "Search Canceled!" NOWAIT
ENDIF

CLEAR READ

 

[TheRambler]

Can you show us which is the index expression you used to create newkey tag?
Is it ALLTRIM(task)+DTOC(compdate)?

 

[StacyStacy]

Yes, it is:
ALLTRIM(task)+DTOC(compdate)
Thanks!

 

[dbMark]

Typically when you SET NEAR ON you also want to SET EXACT OFF. Would that help here?

dbMark

 

[StacyStacy]

I tried but it didn't work. What did I do wrong? Here's the code:
GOTO TOP
SET NEAR ON
SET EXACT OFF

IF m.find = 1 AND NOT EMPTY(m.findthis)
SELECT * ;
FROM bashproc ;
WHERE bashproc.task = ALLTRIM(m.findthis) ;
INTO CURSOR pickone

IF _tally > 0 && I found at least one record that matched......
SELECT pickone
BROWSE
m.f1=pickone.task
m.f2=pickone.compdate
SELECT bashproc
SET ORDER TO TAG newkey
IF SEEK(ALLTRIM(pickone.task)+DTOC(pickone.compdate),"bashproc")
SELECT bashproc
SCATTER MEMVAR MEMO
SET ORDER TO
CLEAR READ
RETURN
ELSE
WAIT WINDOW "Seek Into BASHProc Failed"
ENDIF

ELSE
WAIT WINDOW "No Matching Record(s) Found"
ENDIF
ELSE
WAIT WINDOW "Search Canceled!" NOWAIT
ENDIF

CLEAR READ

 

[dbMark]

Typically when you SET NEAR ON you also want to SET EXACT OFF. Would that help here?

I should explain that statement better. You would often do the above, using SET EXACT OFF also, if your search string could be smaller than the index key expression.

 

[StacyStacy]

I am so clueless. Can you assist me in writing the code correctly? I am new to FoxPro 2.6 and learning to write the code. So sorry and thanks so much

 

[TheRambler]

If you get the message "No Matching Record(s) found", then that means _tally = 0. Maybe you can try changing the where clause to:

WHERE UPPER(bashproc.task) = UPPER(ALLTRIM(m.findthis))

or maybe add SET DELETED OFF, in case the record you are looking for is deleted.

 

[DSummZZZ]

There are a couple confusing things about NEAR seeks, =, and so on. Let me see if I can help.
First of all, when doing a SEEK with SET NEAR ON, you will get the closest matching record. If an exact match is not found, the record pointer will be positioned on the record following where the SEEK ended. For example, say you have the following three records. I'll put them in order since that's how the index will find them:

'Fred Flagstone '
'Fred Flinstone '
'Fred Slate '

With SET NEAR ON, a seek of ALLTRIM('Fred Flintrock') would postion the record pointer to record 2, because alphabetically rock comes before stone, but the exact match wasn't found. Of course, with SET NEAR OFF the record pointer would be at end of file.

Now notice also the spaces in the records. Running the SELECT as you did above with:
WHERE bashproc.task = ALLTRIM(m.findthis)
you are attempting to match
'Fred Flintstone ' with ALLTRIM('Fred Flintstone ') wich evaluates to
'Fred Flintstone ' = 'Fred Flintstone'.
(Rvalue has no spaces). Since lvalue is compared with rvalue, it isn't a match.

Try swapping them:
WHERE ALLTRIM(m.findthis) = bashproc.task

or TRIMming them both:
WHERE ALLTRIM(bashproc.task) = ALLTRIM(m.findthis)

Hope this helps a little.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[StacyStacy]

Thanks so much!

 

[TheRambler]

If you didn't solve your problem yet, try SET ANSI OFF.

Determines how comparisons between strings of different lengths are made with the = operator in FoxPro SQL commands.
...
When SET ANSI is OFF, the shorter string isn't padded with blanks. The two strings are compared character for character until the end of the shorter string is reached.
...
String Order In SQL commands, the left-to-right order of the two strings in a comparison is irrelevant — switching a string from one side of the = or == operator to the other doesn't affect the result of the comparison.

Also, after looking at your code again, I don't think you need SET NEAR at all because your SELECT statement is getting the records from bashproc, there must be an exact match when you SEEK a record.

And finally, build your index again but don't use ALLTRIM nor DTOC. Instead, your index expression should be:
INDEX ON task + DTOS(compdate) TAG yourtag ADDITIVE

Maybe someone else can explain further why you shouldn't trim a field in an index expression. I use DTOS instead of DTOC because the character date returned doesn't depend on SET CENTURY nor SET DATE.

 

[TheRambler]

I found the explanation I was looking for in thread182-798417

...when you create an index using ALLTRIM(), Fox looks at the first record to match the criteria (i.e., 'MARTIN', which is 6 chars long) then bases the length of the key on the length of the ALLTRIM'ed value. So from there on out, Fox will olny 'see' the first 6 characters of the field, and index them on that.

Isn't FoxPro fun? that's why I like it.

 

[StacyStacy]

Thanks sooo much. Here's a solution that was found. It consisted of writing the code, "UPPER". BUT ... believe me, your help is not in vail. I have soooo much to learn. The basic concept is easy, it's just knowing how to write it to get it to work .... Can you suggest how you learned to write code for FoxPro 2.6 quickly?

Thanks...here's the code:
IF m.find = 1 AND NOT EMPTY(m.findthis)
SELECT * ;
FROM bashproc ;
WHERE UPPER(bashproc.task) = ALLTRIM(UPPER(m.findthis)) ;
INTO CURSOR pickone

IF _tally > 0 && I found at least one record that matched......
ON KEY LABEL ENTER KEYBOARD '{Ctrl+F4}'
SELECT pickone
BROWSE
ON KEY LABEL ENTER
m.f1=pickone.task
m.f2=pickone.compdate
SELECT bashproc
SET ORDER TO TAG newkey
IF SEEK(ALLTRIM(UPPER(pickone.task))+DTOC(pickone.compdate),"bashproc")
SELECT bashproc
SCATTER MEMVAR MEMO
SET ORDER TO
CLEAR READ
RETURN
ELSE
WAIT WINDOW "Seek Into BASHProc Failed"
ENDIF

ELSE
WAIT WINDOW "No Matching Record(s) Found"
ENDIF
ELSE
WAIT WINDOW "Search Canceled!" NOWAIT
ENDIF

CLEAR READ

 

[TheRambler]

You are welcome, good you could solve it. I see you had no problem using ALLTRIM in your index expression, so maybe we should reconsider that idea.
The best advice I could offer you is: learn Visual FoxPro.