Help with Field properties

[DMudra2002]

This has got to be an easy question with an easy solution. I have a field that I import escrow numbers too. The field consists of numbers and letters and (sometimes) dashes. I compare this field to another database to match up these escrow numbers. Unless these numbers match exactly it doesn't come up on the search even though they are the same escrow number

So if the one escrow number is 55423GH it will not match up to 55423gh because it is not capitalized. It would solve the problem if I could simply make the field properties stipulate CAPITALS only so if I imported "55423gh" into that field it would show up as "55423GH". Any suggestions?

It would even be better if we could eliminate the dashes because some may show up as "55423-GH".

 

[tviman]

There are several ways to accomplish this depending on what database system you're using.

If you're using a dBase III+ compatible system then there is no way to set &quot;field rules&quot; that require capital letters. This must be done programatically and the easiest way to accomplish this is to capitalize the numbers during the import process with a statement like toupper(<fieldname>). This changes all alpha characters to uppercase. Or, you can import as you have been but use the same funtion in your search command: seek toupper(<fieldname>). This does the same thing but is a little slower due to the &quot;conversion on the fly&quot; process.

Another thing you might want to look at is the structure of the index file(s). I've seen a lot of indexing statements that trim off any trailing spaces as well as using the above function to capitalize the index values.

Good luck....

There's always a better way...

 

[dbMark]

To force the computer to look at alpha characters in upper case, use the UPPER() function on both fields when doing the compare. (Of course, equally valid would be to do the same thing but instead using the LOWER() function, but I'm not sure if earlier versions include this function.)

As mentioned above, many programmers ceate indexes that force all characters to uppercase to eliminate the possibility of getting records out of the expected order. As a suggestion, if at all possible, try to get the data when entered into the system to be converted to upper or lower case. In other words, get the data fixed at the entry point unless unavoidable.

INDEX ON UPPER(escrow_num) TAG escrow && for .MDX
INDEX ON UPPER(escrow_num) TO escrow  && for .NDX

The test you need could be something like this example, with changes made to fit an IF or SEEK:

LOCATE FOR UPPER(A->escrow_num) = UPPER(B->escrow_num)

To ignore the occasional hyphen, use this combination of AT() and STUFF() functions. As you can see your hypen stripping code will either have to be made into a function or the compare will take up more than one line:

* First put the data into a work variable
* Trim spaces so final compare can ignore unequal lengths and trailing spaces issues
* If hyphen exists, remove it

cx1=LTRIM(RTRIM(A->escrow_num))
cx1=UPPER(IIF(AT("-",cx1)=0,cx1,STUFF(cx1,AT("-",cx1),1,"")))

cx2=LTRIM(RTRIM(B->escrow_num))
cx2=UPPER(IIF(AT("-",cx2)=0,cx2,STUFF(cx2,AT("-",cx2),1,"")))

IF cx1=cx2  && THEY MATCH!
*** your code here ***
ENDIF

I have just today uploaded a FAQ which includes emulation of the FoxPro command STRTRAN(), among others, which will replace/remove multiple occurrences of text from a string.
faq290-3518